BookmarkSubscribeRSS Feed
BCNAV
Quartz | Level 8

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

5 REPLIES 5
Kurt_Bremser
Super User

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;

 

ballardw
Super User

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.

 

 

BCNAV
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 474 views
  • 0 likes
  • 3 in conversation