topic Looping in a Datastep in SAS Programming

Looping in a Datastep

BCNAV — Thu, 24 May 2018 13:03:35 GMT

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

Re: Looping in a Datastep

Kurt_Bremser — Thu, 24 May 2018 13:22:36 GMT

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;

Re: Looping in a Datastep

ballardw — Thu, 24 May 2018 14:19:51 GMT

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.

Re: Looping in a Datastep

BCNAV — Thu, 24 May 2018 20:02:53 GMT

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

Re: Looping in a Datastep

Kurt_Bremser — Thu, 24 May 2018 20:15:17 GMT

If you transpose to a long dataset, the code can be made completely data-driven.

Re: Looping in a Datastep

Kurt_Bremser — Fri, 25 May 2018 06:32:04 GMT

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.