topic Re: How to systematically process many variables once for all?! in SAS Programming

How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:11:44 GMT

I have one macro which CSUMs up one variable in one dataset. It runs fine. But the issue is the data is huge with lots of variables

waiting for same CSUM process. If process one variable, it is far away from being efficient.

How to process in a systematic way? Once for all variables? Say %let varlist=var1, var2, var3, ..., var100;

If var1/2/3/.../100, read in as 100 rows in a temp dataset, then generate the code in a systematic way. I can do that.

The quest is then that how to turn the varlist into a dataset(one variable name for one row)?!

Thanks,

%macro cumsum1by(ds, var, byvar); data &ds.(DROP = ); /*data &ds.(DROP = );*/ set &ds.; by &byvar.; RETAIN &var._csum ; &var._csum=coalesce(&var.,0)+coalesce(&var._csum,0) ; IF FIRST.&byvar. THEN &var._csum = coalesce(&var.,0) ; RUN ;quit; %mend;

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:13:37 GMT

The quest is how to turn a string line, like below, into a dataset(one string for one row).

%let varlist=var1, var2, var3, ..., var100;

Re: How to systematically process many variables once for all?!

PaigeMiller — Thu, 27 Jan 2022 13:27:09 GMT

It seems you have a longer range goal, and you are struggling with how to get there, and you have chosen the wrong tools. Arrays would allow you to pass through the data once, instead of macros that pass through the data 100 times, and is relatively simple to program.

Also, I don't understand this:

If var1/2/3/.../100, read in as 100 rows in a temp dataset,

Variable names in rows? SAS doesn't work that way.

What is a CSUM? Is it cumulative sum? If so, look at PROC EXPAND.

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:28:03 GMT

CSUM = cumulative sum

The goal here is to CSUM up lots of variables in one dataset with by variables.

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:31:46 GMT

Trying below. The wanted outcome is(one var in a row/dataset):

var1

var2

var3

var4

var5

varx

%let varlist=var1 var2 var3 var4 var5 var6 ;

data _temp;
input varname;
datalines;
&varlist.
;
run;quit;

Re: How to systematically process many variables once for all?!

PaigeMiller — Thu, 27 Jan 2022 13:32:37 GMT

PROC EXPAND is the answer.

proc expand data=have out=want;
    by byvariable;
    convert var1-var100 / transformout = cusum;
run;

Re: How to systematically process many variables once for all?!

PaigeMiller — Thu, 27 Jan 2022 13:43:49 GMT

Adding: this is why you should state the overall goal clearly, before asking questions about the details of how you create a specific string of text.

SAS has programmed many of the standard data manipulation operations into various different PROCs, and it is frustrating to me to see people work really really really really really really hard to do something that SAS has already programmed. So, state the overall goal clearly, before you get into the details. In many cases, the overall goal has already been programmed.

Next time you get lost in the details of programming, maybe the better question to ask is: what is a better way to get there.

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:43:19 GMT

Thanks. But the code is not runable.

What I want is that turn the string line into a dataset, which has one string in one row.

%let varlist=var1 var2 var3 var4 var5 var6 ;

Re: How to systematically process many variables once for all?!

PaigeMiller — Thu, 27 Jan 2022 13:45:53 GMT

What is the reason it is not runable?

I still don't think your approach of manipulating strings is the answer. ARRAYs can also do the job, no creation of text strings needed.

Re: How to systematically process many variables once for all?!

Ksharp — Thu, 27 Jan 2022 13:53:36 GMT

Do you have SAS/IML. It is very easy for IML.



proc iml;
use sashelp.class;
read all var _num_ into x[c=vname];
print x[c=vname l=''];

do i=1 to ncol(x);
y=y||cusum(x[,i]);
end;

print y[c=('cum_'+vname) l=''];
quit;

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 13:56:27 GMT

Thanks a huge. Sorry I never touch IML before.

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 14:00:16 GMT

The outcome I want is the top half. The issue is the bottom half is not working.

data _temp; input varname $ @@; datalines; var1 var2 var3 var4 var5 var6 ; run;quit;

_______________________________________________

%let varlist=var1 var2 var3 var4 var5 var6 ;

data _temp2; input varname $ @@; datalines; &varlist. ; run;quit;

Re: How to systematically process many variables once for all?!

PaigeMiller — Thu, 27 Jan 2022 14:48:47 GMT

Honestly, I'll pass on this string handling issue. I think it is unnecessary, and complicated, but I'm still not 100% sure I understand what you want.

If you want cumulative sums, you can do that without going through all of this string handling. Do you want to see that, or not? And why doesn't PROC EXPAND work?

Re: How to systematically process many variables once for all?!

FreelanceReinh — Thu, 27 Jan 2022 14:37:30 GMT

Hi @hellohere,

@hellohere wrote:

The issue is the bottom half is not working.

(...)

%let varlist=var1 var2 var3 var4 var5 var6 ;

data _temp2; input varname $ @@; datalines; &varlist. ; run;quit;

That's because macro variable references are not resolved in data lines. But you can create and submit ("include") the DATA step code you envisioned:

filename vlist temp;

data _null_;
file vlist;
put 'data _temp; input varname :$32. @@; datalines;' / "&varlist" / ';';
run;

%inc vlist;

Re: How to systematically process many variables once for all?!

Tom — Thu, 27 Jan 2022 15:31:11 GMT

To convert a list in macro variable into a series of observations just use a DO loop.

%let list=a b c d;
data list;
  length index 8 name $32 ;
  do index=1 to countw("&list");
    name=scan("&list",index);
    output;
  end;
run;

But why?

To generate a sum many variables just use arrays.

%let source=height weight age;
%let target=c_height c_weight c_age;
proc sort data=sashelp.class out=have;
  by sex;
run;

data want;
  do until(last.sex);
    set have ;
    by sex;
    array in &source;
    array out &target;
    do index=1 to dim(in);
       out[index]=sum(out[index],in[index]);
    end;
    output;
  end;
  drop index;
run;

If you want to generate the target names from the source names you could use something like:

%let target=c_%sysfunc(tranwrd(&source,%str( ),%str( c_)));

Re: How to systematically process many variables once for all?!

s_lassen — Thu, 27 Jan 2022 15:44:34 GMT

As I read you initial macro, it does this:

%macro cumsum1by(ds, var, byvar); 
  data &ds.(DROP = );
  set &ds.; 
  by &byvar.; 
  RETAIN &var._csum ; 
  &var._csum=coalesce(&var.,0)+coalesce(&var._csum,0) ; 
  IF FIRST.&byvar. THEN &var._csum = coalesce(&var.,0) ;
RUN ;
%mend;

And you want to do that with a variable list instead of a single variable. Something like this may work:

%macro cumsum1by(ds, vars, byvar,drop=,out=&ds); 
  %local i var lastby n_vars;
  %let lastby=%scan(&byvar,%sysfunc(countw(&byvar)));
  %let n_vars=%sysfunc(countw(&vars));
  data &out(DROP=&drop );
    set &ds.; 
    by &byvar.; 
    if first.&lastby then do;
      %do i=1 %to &n_vars;
         %let var=%scan(&vars,&i);
         &var._csum=0;
         %end;
      end;
     %do i=1 %to &n_vars;
         %let var=%scan(&vars,&i);
         &var._csum+&var;
         %end;
 RUN ;
%mend;

I put in the DROP= parameter because it looked like you had thought about creating one. And I put in an OUT= parameter; although your initial macro overwrites the original dataset (which is also the default in the new version), you may want to do something else.

And then I changed the use of RETAIN and COALESCE to a SUM statement (&var._csum+&var;), which is very nifty for stuff like this: the variable added gets automatically coalesced with zero, and the receiving variable gets automatically retained.

I also put in a calculation of the last of a BY variable list (&LAST_BY), in case you want to use more than one BY variable.

So an example of a call could be this

cumsum1by(work.have,cash owed left,client_id month,drop=a c,out=test);

which will summarize the variables CASH, OWED and LEFT from WORK.HAVE by CLIENT_ID and MONTH, and output it to TEST, dropping variables A and C.

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 17:50:22 GMT

Thanks a huge

Re: How to systematically process many variables once for all?!

hellohere — Thu, 27 Jan 2022 18:07:51 GMT

Thanks a lot still. To CSum by var in a dataset, here are many ways to do so. The easiest one is the one with IML, which

I do not use. I bet your way surely works also.

I am sticky with converting string-token line into rows in a dataset, because that is a way I know how to systematically generate

code line for variables. The variable names are fluid, not just var1/var2/var3/... and not just CSum