The SASEFAME Interface Engine: Creating a View Using the SQL Procedure and SASEFAME

The SASEFAME Interface Engine

Example 34.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 as shown in Output 34.5.1. Refer to the BASE SAS Procedures Guide for details on 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 34.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 DRI Basic Economic data by using the SQL procedure’s FROM and USING clauses as shown in Output 34.5.2.

   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 34.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.00
4	1949	19003.00
5	1950	24960.00
6	1951	21906.00
7	1952	20246.00
8	1953	20912.00
9	1954	21056.00
10	1955	27168.00
11	1956	27638.00
12	1957	26723.00
13	1958	22929.00
14	1959	29729.00
15	1960	28444.00
16	1961	28226.00
17	1962	32396.00
18	1963	34932.00
19	1964	40024.00
20	1965	47941.00
21	1966	51429.00
22	1967	49164.00
23	1968	51208.00
24	1969	49371.00
25	1970	44034.00
26	1971	52352.00
27	1972	62644.00
28	1973	81645.00
29	1974	91028.00
30	1975	89494.00
31	1976	109492.00
32	1977	130260.00
33	1978	154357.00
34	1979	173428.00
35	1980	156096.00
36	1981	147765.00
37	1982	113216.00
38	1983	133495.00
39	1984	146448.00
40	1985	128521.99
41	1986	111337.99
42	1987	160785.00
43	1988	210532.00
44	1989	201637.00
45	1990	218702.00
46	1991	210666.00
47	1992	.
48	1993	.

The following statements create a view of the DB77 database by using the SQL procedure’s FROM and USING clauses, as shown in Output 34.5.3.

   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 34.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 DRI economic database by using the SQL procedure’s FROM and USING clauses, as shown in Output 34.5.4.

   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;

Note that the SAS option VALIDVARNAME=ANY was used at the beginning of this example due to special characters being 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. Note that different engine options could have been used in the USING LIBNAME clause if desired.

Output 34.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	1.29990
2	1981	1.09574
3	1982	1.05862
4	1983	1.70580
5	1984	1.76351
6	1985	1.74258
7	1986	1.81205
8	1987	1.62914
9	1988	1.48748
10	1989	1.38218
11	1990	1.20161
12	1991	1.00878
13	1992	1.20159
14	1993	1.29201
15	1994	1.44669
16	1995	1.36158
17	1996	1.46952
18	1997	1.47760
19	1998	1.56250

Top of Page