The SASEFAME Interface Engine

Example 40.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 40.5.1 shows the results.

Output 40.5.1 Printout of the Fame View of OECD Data

famesql5: PROC SQL Dual Embedded Libraries w/ FAME option

OECD1: Print of View from Embedded Library with FAME Option

Obs	DATE	FIN.HERD
1	1985	1097.00
2	1986	1234.00
3	1987	1401.30
4	1988	1602.00
5	1989	1725.50
6	1990	1839.00
7	1991	.

The following statements create a view of Data Resources Inc. (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 40.5.2 shows the results.

Output 40.5.2 Printout of the Fame View of DRI Basic Economic Data

famesql5: PROC SQL Dual Embedded Libraries w/ FAME option

SUBECON: Print of View from Embedded Library with FAME Option

Obs	DATE	GAA
1	1946	.
2	1947	.
3	1948	23174
4	1949	19003
5	1950	24960
6	1951	21906
7	1952	20246
8	1953	20912
9	1954	21056
10	1955	27168
11	1956	27638
12	1957	26723
13	1958	22929
14	1959	29729
15	1960	28444
16	1961	28226
17	1962	32396
18	1963	34932
19	1964	40024
20	1965	47941
21	1966	51429
22	1967	49164
23	1968	51208
24	1969	49371
25	1970	44034
26	1971	52352
27	1972	62644
28	1973	81645
29	1974	91028
30	1975	89494
31	1976	109492
32	1977	130260
33	1978	154357
34	1979	173428
35	1980	156096
36	1981	147765
37	1982	113216
38	1983	133495
39	1984	146448
40	1985	128522
41	1986	111338
42	1987	160785
43	1988	210532
44	1989	201637
45	1990	218702
46	1991	210666
47	1992	.
48	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 40.5.3 shows the results.

Output 40.5.3 Printout of the Fame View of DB77 Data

famesql5: PROC SQL Dual Embedded Libraries w/ FAME option

DB77: Print of View from Embedded Library with FAME Option

Obs	DATE	ANN	QANDOM.X
1	1959	.	0.56147
2	1960	.	0.51031
3	1961	.	.
4	1962	.	.
5	1963	.	.
6	1964	.	.
7	1965	.	.
8	1966	.	.
9	1967	.	.
10	1968	.	.
11	1969	.	.
12	1970	.	.
13	1971	.	.
14	1972	.	.
15	1973	.	.
16	1974	.	.
17	1975	.	.
18	1976	.	.
19	1977	.	.
20	1978	.	.
21	1979	.	.
22	1980	100	.
23	1981	101	.
24	1982	102	.
25	1983	103	.
26	1984	104	.
27	1985	105	.
28	1986	106	.
29	1987	107	.
30	1988	109	.
31	1989	111	.

The following statements create a view of the Data Resources Incorporated (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 40.5.4 shows the results.

Output 40.5.4 Printout of the Fame View of DRI Basic Economic Data

famesql5: PROC SQL Dual Embedded Libraries w/ FAME option

DRIECON: Print of View from Embedded Library with FAME Option

Obs	DATE	HUSTS
1	1980	1292.2
2	1981	1084.2
3	1982	1062.2
4	1983	1703.0
5	1984	1749.5
6	1985	1741.8
7	1986	1805.4
8	1987	1620.5
9	1988	1488.1
10	1989	1376.1
11	1990	1192.7
12	1991	1013.9
13	1992	1199.7
14	1993	1287.6
15	1994	1457.0
16	1995	1354.1
17	1996	1476.8
18	1997	1474.0
19	1998	1616.9
20	1999	1666.5
21	2000	1568.7
22	2001	1602.7
23	2002	1704.9
24	2003	.