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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.