Example 5.5: Creating a View using SQL Procedure and SASEFAME

The SASEFAME Interface Engine

Example 5.5: Creating a View using SQL Procedure and SASEFAME

This example creates a view using the SQL procedure's from and using clauses. See "SQL Procedure Guide, Version 7" for details on SQL views.

                               
 title1 'famesql5: PROC SQL Dual Embedded Librarys w/ FAME option';
 options validvarname=any;
         
 /* Dual Embedded Library Allocations (With FAME Option) */
 /********************************************************/
         
 /* OECD1 Database */
 /******************/
         
 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 '/economic/databases/testdat' 
                       convert=(tech=constant freq=annual),
          libname temp '/usr/local/scratch/mine'
    quit;

 title2 'OECD1: Print of View from Embedded Library with Option';
 proc print data=fameview;
 run;

Output 5.5.1: Printout of the fameview of OECD data

PROC SQL Dual Embedded Librarys w/ FAME option

OECD1: Print of View from Embedded Library with Option

Obs	DATE	FIN.HERD
1	1985	1097.0
2	1986	1234.0
3	1987	1401.3
4	1988	1602.0
5	1989	1725.5
6	1990	1839.0
7	1991	.


 /* SUBECON Database */
 /********************/
         
 title2 'SUBECON: Dual Embedded Library Allocations w/ FAME Option';
 proc sql;
    create view fameview as
    select date, gaa
    from lib1.subecon
    using libname lib1 sasefame '/economic/databases/testdat' 
                       convert=(tech=constant freq=annual),
          libname temp '/usr/local/scratch/mine'
    quit;

 title2 'SUBECON: Print of View from Embedded Library with Option';
 proc print data=fameview;
 run;

Output 5.5.2: Printout of the fameview of DRI Basic Ecomonic data

PROC SQL Dual Embedded Librarys w/ FAME option

SUBECON: Print of View from Embedded Library with 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	.


 title2 'DB77: Dual Embedded Library Allocations with FAME Option';
 proc sql;
    create view fameview as
    select date, ann, 'qandom.x'n
    from lib1.db77
    using libname lib1 sasefame '/economic/databases/testdat' 
                       convert=(tech=constant freq=annual),
          libname temp '/usr/local/scratch/mine'
    quit;

   title2 'DB77: Print of View from Embedded Library with Option';
   proc print data=fameview;
   run;

Output 5.5.3: Printout of the fameview of DB77 data

PROC SQL Dual Embedded Librarys w/ FAME option

DB77: Print of View from Embedded Library with 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	.


 /* DRIECON Database */
 /********************/
         
 title2 'DRIECON: Dual Embedded Library Allocations w/ FAME Option';
 proc sql;
    create view fameview as
    select date, husts
    from lib1.driecon
    using libname lib1 sasefame '/economic/databases/testdat' 
                       convert=(tech=constant freq=annual),
          libname temp '/usr/local/scratch/mine'
    quit;
         
 title2 'DRIECON: Print of View from Embedded Library with Option';
 proc print data=fameview;
 run;

Note that the SAS option VALIDVARNAME=ANY was used at the top of this example due to special characters being present in the time series names. The output from this example shows how each fameview 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 5.5.4: Printout of the fameview of DRI Basic Ecomonic data

PROC SQL Dual Embedded Librarys w/ FAME option

DRIECON: Print of View from Embedded Library with Option

Obs	DATE	HUSTS
1	1947	1.26548
2	1948	1.33470
3	1949	1.43617
4	1950	1.90041
5	1951	1.43759
6	1952	1.44883
7	1953	1.40279
8	1954	1.53525
9	1955	1.61970
10	1956	1.32400
11	1957	1.17300
12	1958	1.31717
13	1959	1.53450
14	1960	1.25505
15	1961	1.31188
16	1962	1.45996
17	1963	1.58858
18	1964	1.53950
19	1965	1.46966
20	1966	1.16507
21	1967	1.28573
22	1968	1.50314
23	1969	1.48531
24	1970	1.43565
25	1971	2.03775
26	1972	2.36069
27	1973	2.04307
28	1974	1.32855
29	1975	1.16164
30	1976	1.53468
31	1977	1.96218
32	1978	2.00184
33	1979	1.71847
34	1980	1.29990
35	1981	1.09574
36	1982	1.05862
37	1983	1.70580
38	1984	1.76351
39	1985	1.74258
40	1986	1.81205
41	1987	1.62914
42	1988	1.48748
43	1989	1.38218
44	1990	1.20161
45	1991	1.00878
46	1992	1.20159
47	1993	1.29201
48	1994	1.44684
49	1995	1.35845
50	1996	1.48336

Chapter Contents
Previous
Next
Top