topic Re: SQL Select using Macro to Resolve Columns to Include in SAS Programming

SQL Select using Macro to Resolve Columns to Include

lowjack — Fri, 24 Sep 2010 16:57:18 GMT

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;

Re: SQL Select using Macro to Resolve Columns to Include

WaltSmith — Fri, 24 Sep 2010 17:44:39 GMT

The following solution uses your table of column names which I called "datasetofcolnames" in the code below:
[pre]
%macro sumCols( _collist );
%local return n_cols colname i;
%let n_cols = %sysfunc( countw( &_collist, %str( )));
%do i=1 %to &n_cols;
%let colname = %scan( &_collist, &i, %str( ));
%let return = &return sum(&colname) as &colname;
%if ( &i < &n_cols ) %then
%let return = &return,;
%end;
&return
%mend;
proc sql noprint;
*-- first get a list of the column names ;
select distinct colname into : collist separated by ' '
from datasetofcolnames
order by colname;
*-- then extract your data ;
create table testMacro as
select row_labels, month, %sumCols( &collist )
from Me
group by row_labels, month;
quit;
[/pre]

Re: SQL Select using Macro to Resolve Columns to Include

art297 — Fri, 24 Sep 2010 17:49:51 GMT

Chad,

Unless you are just trying to learn how to write macros, I would avoid using a macro for this problem. Instead, you can use proc sql to create a macro variable that does what you want. For example:

/* Create some test data */
data test2;
set sashelp.class (rename=(
name=row_labels
age=Col0001
height=Col0002
weight=Col0003
));
month=mod(_n_,3);
run;

/*Create a macro variable with proc sql */
proc sql noprint;
select "Sum ("||trim(name)||") as Sum"||substr(name,4)
into :sumCol separated by ","
from dictionary.columns
WHERE libname eq "WORK"
and memname = "TEST2"
and name like 'Col%';
quit;

proc sql noprint;
create table testMacro as
select row_labels, &sumCol.
from test2
group by row_labels,month;
quit;

HTH,
Art

Re: SQL Select using Macro to Resolve Columns to Include

data_null__ — Fri, 24 Sep 2010 18:07:36 GMT

I find that PROCs SUMMARY or MEANS are much better suited to the task than SQL. SQL is fine but the syntax does not make it easy like MEANS/SUMMARY.

[pre]
data have;
array _c

Col10599 Col10608 Col05393;
do _n_ = 1 to 100;
do _i_ = 1 to dim(_c);
_c[_i_] = rannor(1)*10 + 5;
end;
output;
end;
drop _:;
run;
proc summary data=have;
var col:;
output out=sum;
run;
proc print;
run;
[/pre]

Re: SQL Select using Macro to Resolve Columns to Include

lowjack — Fri, 24 Sep 2010 18:19:44 GMT

Thank you! This works perfectly.

Re: SQL Select using Macro to Resolve Columns to Include

WaltSmith — Fri, 24 Sep 2010 18:20:40 GMT

Agree completely - when I need sums or means or ... its almost always proc summary or proc means (when I want quantiles, or medians, etc). I write less code using proc summary than the equivalent code using SQL, however, SQL can sometimes do what it may take several steps otherwise.

With the simple problem presented, I would prefer proc summary, but he asked in the framework of proc sql so ...

Re: SQL Select using Macro to Resolve Columns to Include

data_null__ — Fri, 24 Sep 2010 18:25:35 GMT

I was "speaking" more to the OP's statement that he is new to SAS. Thinking that learning MEANS/SUMMARY would be more fulfilling than using SQL for summary statistics.