Example 36.5 Creating a View Using the SQL Procedure and SASEFAME
The following statements create a view of OECD data by using the SQL procedure’s FROM and USING clauses: See the BASE SAS Procedures Guide for details about SQL views.
title1 'famesql5: PROC SQL Dual Embedded Libraries w/ FAME option';
options validvarname=any;
%let FAME=%sysget(FAME);
%put(&FAME);
%let FAMETEMP=%sysget(FAME_TEMP);
%put(&FAMETEMP);
title2 'OECD1: Dual Embedded Library Allocations with FAME Option';
proc sql;
create view fameview as
select date, 'fin.herd'n
from lib1.oecd1
using libname lib1 sasefame "%sysget(FAME_DATA)"
convert=(tech=constant freq=annual),
libname temp "%sysget(FAME_TEMP)";
quit;
title2 'OECD1: Print of View from Embedded Library with FAME Option';
proc print data=fameview;
run;
Output 36.5.1 shows the results.
Output 36.5.1
Printout of the Fame View of OECD Data
1985 |
1097.00 |
1986 |
1234.00 |
1987 |
1401.30 |
1988 |
1602.00 |
1989 |
1725.50 |
1990 |
1839.00 |
1991 |
. |
The following statements create a view of DRI Basic Economic data by using the SQL procedure’s FROM and USING clauses:
title2 'SUBECON: Dual Embedded Library Allocations with FAME Option';
options validvarname=any;
%let FAME=%sysget(FAME);
%put(&FAME);
%let FAMETEMP=%sysget(FAME_TEMP);
%put(&FAMETEMP);
proc sql;
create view fameview as
select date, gaa
from lib1.subecon
using libname lib1 sasefame "%sysget(FAME_DATA)"
convert=(tech=constant freq=annual),
libname temp "%sysget(FAME_TEMP)";
quit;
title2 'SUBECON: Print of View from Embedded Library with FAME Option';
proc print data=fameview;
run;
Output 36.5.2 shows the results.
Output 36.5.2
Printout of the Fame View of DRI Basic Economic Data
1946 |
. |
1947 |
. |
1948 |
23174 |
1949 |
19003 |
1950 |
24960 |
1951 |
21906 |
1952 |
20246 |
1953 |
20912 |
1954 |
21056 |
1955 |
27168 |
1956 |
27638 |
1957 |
26723 |
1958 |
22929 |
1959 |
29729 |
1960 |
28444 |
1961 |
28226 |
1962 |
32396 |
1963 |
34932 |
1964 |
40024 |
1965 |
47941 |
1966 |
51429 |
1967 |
49164 |
1968 |
51208 |
1969 |
49371 |
1970 |
44034 |
1971 |
52352 |
1972 |
62644 |
1973 |
81645 |
1974 |
91028 |
1975 |
89494 |
1976 |
109492 |
1977 |
130260 |
1978 |
154357 |
1979 |
173428 |
1980 |
156096 |
1981 |
147765 |
1982 |
113216 |
1983 |
133495 |
1984 |
146448 |
1985 |
128522 |
1986 |
111338 |
1987 |
160785 |
1988 |
210532 |
1989 |
201637 |
1990 |
218702 |
1991 |
210666 |
1992 |
. |
1993 |
. |
The following statements create a view of the DB77 database by using the SQL procedure’s FROM and USING clauses:
title2 'DB77: Dual Embedded Library Allocations with FAME Option';
options validvarname=any;
%let FAME=%sysget(FAME);
%put(&FAME);
%let FAMETEMP=%sysget(FAME_TEMP);
%put(&FAMETEMP);
proc sql;
create view fameview as
select date, ann, 'qandom.x'n
from lib1.db77
using libname lib1 sasefame "%sysget(FAME_DATA)"
convert=(tech=constant freq=annual),
libname temp "%sysget(FAME_TEMP)";
quit;
title2 'DB77: Print of View from Embedded Library with FAME Option';
proc print data=fameview;
run;
Output 36.5.3 shows the results.
Output 36.5.3
Printout of the Fame View of DB77 Data
1959 |
. |
0.56147 |
1960 |
. |
0.51031 |
1961 |
. |
. |
1962 |
. |
. |
1963 |
. |
. |
1964 |
. |
. |
1965 |
. |
. |
1966 |
. |
. |
1967 |
. |
. |
1968 |
. |
. |
1969 |
. |
. |
1970 |
. |
. |
1971 |
. |
. |
1972 |
. |
. |
1973 |
. |
. |
1974 |
. |
. |
1975 |
. |
. |
1976 |
. |
. |
1977 |
. |
. |
1978 |
. |
. |
1979 |
. |
. |
1980 |
100 |
. |
1981 |
101 |
. |
1982 |
102 |
. |
1983 |
103 |
. |
1984 |
104 |
. |
1985 |
105 |
. |
1986 |
106 |
. |
1987 |
107 |
. |
1988 |
109 |
. |
1989 |
111 |
. |
The following statements create a view of the DRI economic database by using the SQL procedure’s FROM and USING clauses:
title2 'DRIECON: Dual Embedded Library Allocations with FAME Option';
options validvarname=any;
%let FAME=%sysget(FAME);
%put(&FAME);
%let FAMETEMP=%sysget(FAME_TEMP);
%put(&FAMETEMP);
proc sql;
create view fameview as
select date, husts
from lib1.driecon
using libname lib1 sasefame "%sysget(FAME_DATA)"
convert=(tech=constant freq=annual)
range='01jan1980'd - '01jan2006'd ,
libname temp "%sysget(FAME_TEMP)";
quit;
title2 'DRIECON: Print of View from Embedded Library with FAME Option';
proc print data=fameview;
run;
The SAS option VALIDVARNAME=ANY is used at the beginning of this example because special characters are present in the time series names. The output from this example shows how each Fame view is the output of the SASEFAME engine’s processing. Different engine options could have been used in the USING LIBNAME clause if desired. Output 36.5.4 shows the results.
Output 36.5.4
Printout of the Fame View of DRI Basic Economic Data
1980 |
1292.2 |
1981 |
1084.2 |
1982 |
1062.2 |
1983 |
1703.0 |
1984 |
1749.5 |
1985 |
1741.8 |
1986 |
1805.4 |
1987 |
1620.5 |
1988 |
1488.1 |
1989 |
1376.1 |
1990 |
1192.7 |
1991 |
1013.9 |
1992 |
1199.7 |
1993 |
1287.6 |
1994 |
1457.0 |
1995 |
1354.1 |
1996 |
1476.8 |
1997 |
1474.0 |
1998 |
1616.9 |
1999 |
1666.5 |
2000 |
1568.7 |
2001 |
1602.7 |
2002 |
1704.9 |
2003 |
. |