BookmarkSubscribeRSS Feed
lowjack
Calcite | Level 5
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;
6 REPLIES 6
WaltSmith
Fluorite | Level 6
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]
art297
Opal | Level 21
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
lowjack
Calcite | Level 5
Thank you! This works perfectly.
data_null__
Jade | Level 19
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]
  • WaltSmith
    Fluorite | Level 6
    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 ...
    data_null__
    Jade | Level 19
    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.

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 6 replies
    • 812 views
    • 0 likes
    • 4 in conversation