Macro Array v3 sql

From sasCommunity
Jump to: navigation, search

Here's a program from my bricolage time this week:

  • given a data set
  • create an array of macro variables
    • i.e. sequentially-numbered macro variables
  • for each column of the data set

Code Challenge: What are the SQL statements to generate a %global statement for each of the items? i.e. either of:

  • individual statements:
    •  %global sex1;
    •  %global sex2;
    •  %global count1;
    •  %global count2;
    •  %global percent1;
    •  %global percent2;
  • or one statement:
    •  %global sex1 sex2 count1 count2 percent1 percent2;

Program

%Let LibName  = sashelp;
%Let MemName  = class;
%Let Name     = Sex;
 
%Let Out_Dim  = dim;
%Let Out_data = Work.&MemName._&Name.;
 
%Let In_data = &Out_Data.;
 
PROC Freq data = &Libname..&MemName.;
          tables &Name.
                 / list missing noprint
             out = &Out_data.;
run;
%put note: SysLast: &SysLast.;
 
*Macro make_array_sql
      (data=
      ,dim =
      );
/**************************      
%if %index(&Data.,.) %then %do;
    %let Libname = %scan(&Data.,1,.);
    %let Memname = %scan(&Data.,2,.);
    %end;
%else %do;
    %let Libname = work;
    %let Memname = &Data;
    %end;
          where  Libname eq "%upcase(&Libname.)"
            and  Memname eq "%upcase(&Memname.)";
 
*****************/
PROC SQL;* noprint; 
          %global &Out_Dim.;
          *describe table dictionary.tables;
          select Nobs
            into :&Out_Dim
            from dictionary.tables
          where  Libname eq "%upcase(%scan(&Syslast.,1,.))"
            and  Memname eq "%upcase(%scan(&Syslast.,2,.))";
            %let &Out_Dim. = &&&Out_Dim.;
            %Put note: Dim: &&&Out_Dim.;
 
          create table Work._List as
          select Name
            from dictionary.columns
          where  Libname eq "%upcase(%scan(&Syslast.,1,.))"
            and  Memname eq "%upcase(%scan(&Syslast.,2,.))";
 
          select Name 
                ,':' !! trim(Name) !! '1 - :' 
                     !! trim(Name) !! '&SysMaxLong.'
          into :ListVars   separated by ', '
              ,:ListArrays separated by ', '
          from Work._List;
          %Put note: ListVars: &ListVars.;
          %Put note: ListArrays: &ListArrays.;
 
          *make macro array;
          select &ListVars.
            into &ListArrays.
            from &In_data.;
 
%symdel ListVars ListArrays;
 
          *testing: list values in log;
          select '%put note: ' !! trim(Name) !! ':' 
                               !! trim(Value) !! ';' 
          into  :List_mvars separated by ' '
          from  dictionary.macros
          where scope eq 'GLOBAL'
            and not(Name like 'SQL%')
            and not(Name like 'SYS%')
          order by name;
          quit;
&List_mvars.;
 
%symdel List_mvars;*contains semicolons;
 
PROC Print  data = Work._List;
            title  Work._List;
PROC Delete data = Work._List;
run;

Log

3          %Let LibName  = sashelp;
4          %Let MemName  = class;
5          %Let Name     = Sex;
6          %Let Out_Dim  = dim;
7          %Let Out_data = Work.&MemName._&Name.;
8          
9          %Let In_data = &Out_Data.;
10         
11         PROC Freq data = &Libname..&MemName.;
12                   tables &Name.
13                          / list missing noprint
14                      out = &Out_data.;
15         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS_SEX has 2 observations and 3 variables.      

16         %put note: SysLast: &SysLast.;
note: SysLast: WORK.CLASS_SEX
17         
18         *Macro make_array_sql
19               (data=
20               ,dim =
21               );
22         /**************************
23         %if %index(&Data.,.) %then %do;
24             %let Libname = %scan(&Data.,1,.);
25             %let Memname = %scan(&Data.,2,.);
26             %end;
27         %else %do;
28             %let Libname = work;
29             %let Memname = &Data;
30             %end;
31                   where  Libname eq "%upcase(&Libname.)"
32                     and  Memname eq "%upcase(&Memname.)";
33         
34         *****************/
35         PROC SQL;
35       !          * noprint;
36                   %global &Out_Dim.;
37                   *describe table dictionary.tables;
38                   select Nobs
39                     into :&Out_Dim
40                     from dictionary.tables
41                   where  Libname eq "%upcase(%scan(&Syslast.,1,.))"
42                     and  Memname eq "%upcase(%scan(&Syslast.,2,.))";
43                     %let &Out_Dim. = &&&Out_Dim.;
44                     %Put note: Dim: &&&Out_Dim.;
note: Dim: 2
45         
46                   create table Work._List as
47                   select Name
48                     from dictionary.columns
49                   where  Libname eq "%upcase(%scan(&Syslast.,1,.))"
50                     and  Memname eq "%upcase(%scan(&Syslast.,2,.))";
NOTE: Table WORK._LIST created, with 3 rows and 1 columns.

51         
52                   select Name
53                         ,':' !! trim(Name) !! '1 - :'
54                              !! trim(Name) !! '&SysMaxLong.'
55                   into :ListVars   separated by ', '
56                       ,:ListArrays separated by ', '
57                   from Work._List;
58                   %Put note: ListVars: &ListVars.;
note: ListVars: Sex, COUNT, PERCENT
59                   %Put note: ListArrays: &ListArrays.;
note: ListArrays: :Sex1 - :Sex2147483647, :COUNT1 - :COUNT2147483647, :PERCENT1 - :PERCENT2147483647
60         
61                   *make macro array;
62                   select &ListVars.
63                     into &ListArrays.
64                     from &In_data.;
65         
66                   *testing: list values in log;
67                   select '%put note: ' !! trim(Name) !! ':'
68                                        !! trim(Value) !! ';'
69                   into  :_List separated by ' '
70                   from  dictionary.macros
71                   where scope eq 'GLOBAL'
72                     and not(Name like 'SQL%')
73                     and not(Name like 'SYS%')
74                   order by name;
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
75                   quit;
NOTE: The PROCEDURE SQL printed pages 1-4.
      

76         &_List.;
note: COUNT1:9
note: COUNT2:10
note: DIM:2
note: IN_DATA:Work.class_Sex
note: LIBNAME:sashelp
note: MEMNAME:class
note: NAME:Sex
note: OUT_DATA:Work.class_Sex
note: OUT_DIM:dim
note: PERCENT1:47.36842
note: PERCENT2:52.63158
note: SEX1:F
note: SEX2:M
77         %symdel _List;*contains semicolons;
78         PROC Print  data = Work._List;
79                     title  Work._List;

NOTE: There were 3 observations read from the data set WORK._LIST.
NOTE: The PROCEDURE PRINT printed page 5.

80         PROC Delete data = Work._List;
81         run;

NOTE: Deleting WORK._LIST (memtype=DATA).

NOTE: This code is now in Macro_Array-Do.

--macro maven == the radical programmer 07:31, 20 April 2012 (EDT)