How to systematically process many variables once for all?!

[Tom · Accepted solution]

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 ⌖
    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_)));

[s_lassen]

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.

[hellohere]

Thanks a huge