Looping in a Datastep

Reply
Frequent Contributor
Posts: 92

Looping in a Datastep

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

Super User
Posts: 10,211

Re: Looping in a Datastep

[ Edited ]

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,508

Re: Looping in a Datastep

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.

 

 

Frequent Contributor
Posts: 92

Re: Looping in a Datastep

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

Super User
Posts: 10,211

Re: Looping in a Datastep

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,211

Re: Looping in a Datastep

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 5 replies
  • 135 views
  • 0 likes
  • 3 in conversation