DATA Step, Macro, Functions and more

SQL Select using Macro to Resolve Columns to Include

Reply
New Contributor
Posts: 2

SQL Select using Macro to Resolve Columns to Include

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;
Contributor
Posts: 22

Re: SQL Select using Macro to Resolve Columns to Include

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]
PROC Star
Posts: 7,471

Re: SQL Select using Macro to Resolve Columns to Include

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
New Contributor
Posts: 2

Re: SQL Select using Macro to Resolve Columns to Include

Thank you! This works perfectly.
Respected Advisor
Posts: 3,799

Re: SQL Select using Macro to Resolve Columns to Include

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]
  • Contributor
    Posts: 22

    Re: SQL Select using Macro to Resolve Columns to Include

    Posted in reply to data_null__
    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 ...
    Respected Advisor
    Posts: 3,799

    Re: SQL Select using Macro to Resolve Columns to Include

    Posted in reply to WaltSmith
    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.
    Ask a Question
    Discussion stats
    • 6 replies
    • 270 views
    • 0 likes
    • 4 in conversation