I would like to rewrite the following macro "sumCol" so it can handle the 300+/- columns that I'd like to sum. There is probably a much more eloquent way of handling this, but I am having trouble finding it because I am new to SAS. If it helps you to redirect me on the right path, the columns I would like to sum all begin with the same three letters "Col". There are typically approx 10 additional columns in the original table that I don't want to sum.
One direction I explored was to use Dictionary.Columns to create a table the column names I would like to sum. This step worked; however I couldn't determine how to iterate the resulting observations to create the desired end result.
Thank you,
Chad
/*BEGIN Code the works (at least for the three hand-keyed columns)*/
%macro sumCol;
sum (Col10599) as Sum10599, sum (Col10608) as Sum30608, sum (Col05393) as Sum05393
%mend;
proc sql;
Create table testMacro as
select row_labels, %sumCol
from Me
group by row_labels month;
quit;
/*END Code the works (at least for the three hand-keyed columns)*/
/* The proc below successfully identifies each column name i'd like to sum */
/* I'm stumped on how to iterate through the results to achieve results */
/* Goal: sum (obs1) as obs1, sum (obs2) as obs2, ... sum (obsN) as obsN */
*Creates a table listing all column names begining with 'TVA' inside table TEST2;
proc sql;
create table meterNames as
select name
from dictionary.columns
WHERE memname = "TEST2"
and name like 'Col%';
quit;
... View more