Monday, December 27, 2010

Sum the columns with irregular names using DICTIONARY.COLUMNS and MACROS

/* Here is an example of using SAS DICTIONARY. If using original data set wide and variables summer1-summer4 which is a string with suffixes having the same construction, it's easy to get the sum of each column in proc sql using macros. However, if we change the name to the different one with no similar structure, then we need to use DICTIONARY to help us with it.  */


data wide;
   input summer1-summer4;
   cards;
   1 2 3 4
   1 2 3 4
   ;
run;

proc datasets;
   modify wide;
   rename summer1 = salary
          summer2 = net
                summer3 = gross
                summer4=adjusted
                ;
run;
quit;

proc sql;
  select name, type
  from dictionary.columns
  where libname='WORK' and    /* here WORK is case sensitive */
        memname='WIDE'        /* here WIDE is case sensitive */
       ;
quit;

proc sql;
   select 'sum(' || trim(name) || ') as sum_' || name
   into :selections separated by ' , '
   from dictionary.columns
   where libname='WORK' and   /* here WORK is case sensitive */
         memname='WIDE' and   /* here WIDE is case sensitive */
               type   ='num'        /* here num is case sensitive */
       ;
quit;

%put &selections;

proc sql;
   select &selections
   from wide
   ;
quit;

No comments:

Post a Comment