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;