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 .