Suppose I have the following:
data EGTASK.ASIA_GRP_GC_WITHIN_TMP;
set EGTASK.ASIA_GRP_WCUTOT_TMP2;
/* Calculate Growth from Same Month in Previous Year, and Contribution to Growth of Group and Total */
WCU_TOT_ASIAGRP_G=WCU_TOT_ASIAGRP/lag12(WCU_TOT_ASIAGRP)-1;
WCU_CGASIA_N01144=(WCU_TOT_N01144-lag12(WCU_TOT_N01144))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N01874=(WCU_TOT_N01874-lag12(WCU_TOT_N01874))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N00369=(WCU_TOT_N00369-lag12(WCU_TOT_N00369))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N00274=(WCU_TOT_N00274-lag12(WCU_TOT_N00274))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N01870=(WCU_TOT_N01870-lag12(WCU_TOT_N01870))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N00346=(WCU_TOT_N00346-lag12(WCU_TOT_N00346))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N01305=(WCU_TOT_N01305-lag12(WCU_TOT_N01305))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N00459=(WCU_TOT_N00459-lag12(WCU_TOT_N00459))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N01296=(WCU_TOT_N01296-lag12(WCU_TOT_N01296))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N00462=(WCU_TOT_N00462-lag12(WCU_TOT_N00462))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N02229=(WCU_TOT_N02229-lag12(WCU_TOT_N02229))/lag12(WCU_TOT_ASIAGRP);
WCU_CGASIA_N01893=(WCU_TOT_N01893-lag12(WCU_TOT_N01893))/lag12(WCU_TOT_ASIAGRP);
run;
Is there a way to clean this up by looping based on a client ID list? I know this can be done in a macro, just not sure if it can be done in a datastep.
for example (pseudo code):
client_list="N01144 N01874 N00369"
loop client_list
WCU_CGASIA_&client_list=(WCU_TOT_&client_list-lag12(WCU_TOT_&client_list))/lag12(WCU_TOT_ASIAGRP);
endloop
thx
Try this with array processing:
data EGTASK.ASIA_GRP_GC_WITHIN_TMP;
set EGTASK.ASIA_GRP_WCUTOT_TMP2;
array WCU_CGASIA{*}
WCU_CGASIA_N01144
WCU_CGASIA_N01874
WCU_CGASIA_N00369
WCU_CGASIA_N00274
WCU_CGASIA_N01870
WCU_CGASIA_N00346
WCU_CGASIA_N01305
WCU_CGASIA_N00459
WCU_CGASIA_N01296
WCU_CGASIA_N00462
WCU_CGASIA_N02229
WCU_CGASIA_N01893
;
array WCU_TOT {*}
WCU_TOT_N01144
WCU_TOT_N01874
WCU_TOT_N00369
WCU_TOT_N00274
WCU_TOT_N01870
WCU_TOT_N00346
WCU_TOT_N01305
WCU_TOT_N00459
WCU_TOT_N01296
WCU_TOT_N00462
WCU_TOT_N02229
WCU_TOT_N01893
;
l_WCU_TOT_ASIAGRP = lag12(WCU_TOT_ASIAGRP);
WCU_TOT_ASIAGRP_G = WCU_TOT_ASIAGRP / l_WCU_TOT_ASIAGRP - 1;
do i = 1 to dim(WCU_CGASIA);
WCU_CGASIA{i} = (WCU_TOT{i} - lag12(WCU_TOT{i})) / l_WCU_TOT_ASIAGRP;
end;
run;
Do you actually have a separate variables for each client???
If so, you might want to reconsider the data structure as I would think you have to constantly add or remove variables as clients change.
It is very likely that BY Group processing based on the clients would work and not require so many variables or code volatility.
I don't think SAS can do this "for each" type of processing. The array doesn't help that much.
I had been hoping to do:
data EGTASK.FAK;
set EGTASK.AMERR_GRP_WCUTOT_TMP2;
WCU_TOT_AMERRGRP_G=WCU_TOT_AMERRGRP/lag12(WCU_TOT_AMERRGRP)-1;
do v = N45604,N01213,N22826;
WCU_CGAMERR_(v) = (WCU_TOT_(v) - lag12(WCU_TOT_(v))) / lag12(WCU_TOT_AMERRGRP);
end;
run;
where the variable v is passed below to get the following from the loop:
WCU_CGAMERR_N45604=(WCU_TOT_N45604-lag12(WCU_TOT_N45604))/lag12(WCU_TOT_AMERRGRP);
WCU_CGAMERR_N01213=(WCU_TOT_N01213-lag12(WCU_TOT_N01213))/lag12(WCU_TOT_AMERRGRP);
WCU_CGAMERR_N22826=(WCU_TOT_N22826-lag12(WCU_TOT_N22826))/lag12(WCU_TOT_AMERRGRP);
It seems such a loop structure is not possible, but would be helpful
-Bill
If you transpose to a long dataset, the code can be made completely data-driven.
A simple example for how to use transpose:
data have;
input month WCU_TOT_AMERRGRP WCU_TOT_N45604 WCU_TOT_N01213;
cards;
1 5 1 2
;
run;
proc transpose data=have out=int;
var WCU_TOT_N:;
by month WCU_TOT_AMERRGRP;
run;
data int2;
set int;
customer = substr(_name_,10);
drop _name_;
run;
/* in here, sort by customer and month, do your calculation with "by customer",
and then sort back by month */
proc transpose data=int2 out=want (drop=_name_) prefix=WCU_TOT_N;
by month WCU_TOT_AMERRGRP;
var col1;
id customer;
run;
/* repeat the last transpose for every newly created variable,
and merge the resulting tables
(one transpose like this can only accept one prefix) */
as you can see, there is never a need to actually loop through the customer names from code, the steps will take care of that for you. By all means, strive for such a solution.
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.