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;
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_)));
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;
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.
CSUM = cumulative sum
The goal here is to CSUM up lots of variables in one dataset with by variables.
PROC EXPAND is the answer.
proc expand data=have out=want;
by byvariable;
convert var1-var100 / transformout = cusum;
run;
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 ;
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.
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;
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?
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
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;
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;
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.
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;
Thanks a huge. Sorry I never touch IML before.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.