topic Re: Clever way to write a long code of new varaibles in SAS Programming

Clever way to write a long code of new varaibles

DaveStar — Sat, 02 Nov 2019 16:59:49 GMT

Hello

I need to run a dynamic report every week.

In the report there are 12 user defined macro variables (m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11).

For each customer there is one row with 13 fields:

X_&m0

X_&m1

X_&m2

X_&m3

X_&m4

X_&m5

X_&m6

X_&m7

X_&m8

X_&m9

X_&m10

X_&m11

In the code there are 3 parts:

part1- calculate % change in var X between each consecutive months

part2-For each % change need to build indicator if there is %increase higher than 50%,or %decrease higher than 50%,or other.

Part3-Need to calculate How many increases in more than 50% occurred and How many decreases in more than 50% occurred

My question:

Is there a way to write this long code by a more clever way?

Please note that by writing a long code as I did there might be typing errors

/*part1- Find % change of X between each  2 consecutive  months*/
PCT_X&m1._&m0.=(X_&m1.-X_&m0.)/X_m0. ;				
PCT_X&m2._&m1.=(X_&m2.-X_&m1.)/X_m1. ;				
PCT_X&m3._&m2.=(X_&m3.-X_&m2.)/X_m2. ;				
PCT_X&m4._&m3.=(X_&m4.-X_&m3.)/X_m3. ;				
PCT_X&m5._&m4.=(X_&m5.-X_&m4.)/X_m4. ;				
PCT_X&m6._&m5.=(X_&m6.-X_&m5.)/X_m5. ;				
PCT_X&m7._&m6.=(X_&m7.-X_&m6.)/X_m6. ;				
PCT_X&m8._&m7.=(X_&m8.-X_&m7.)/X_m7. ;				
PCT_X&m9._&m8.=(X_&m9.-X_&m8.)/X_m8. ;				
PCT_X&m10._&m9.=(X_&m10.-X_&m9.)/X_m9. ;			
PCT_X&m11._&m10.=(X_&m11.-X_&m10.)/X_m10. ;	

/*Part2*/
IF PCT_X&m1._&m0.>50% then Ind_X&m1._&m0.=1;
else IF PCT_X&m1._&m0.<-50% then  Ind_X&m1._&m0.=-1;
else  Ind_X&m1._&m0.=0;

IF PCT_X&m2._&m1.>50% then Ind_X&m2._&m1.=1;
else IF PCT_X&m2._&m1.<-50% then  Ind_X&m2._&m1.=-1;
else  Ind_X&m2._&m1.=0;

IF PCT_X&m3._&m2.>50% then Ind_X&m3._&m2.=1;
else IF PCT_X&m3._&m2.<-50% then  Ind_X&m3._&m2.=-1;
else  Ind_X&m3._&m2.=0;

IF PCT_X&m4._&m3.>50% then Ind_X&m4._&m3.=1;
else IF PCT_X&m4._&m3.<-50% then  Ind_X&m4._&m3.=-1;
else  Ind_X&m4._&m3.=0;


IF PCT_X&m5._&m4.>50% then Ind_X&m5._&m4.=1;
else IF PCT_X&m5._&m4.<-50% then  Ind_X&m5._&m4.=-1;
else  Ind_X&m5._&m4.=0;
	

IF PCT_X&m6._&m5.>50% then Ind_X&m6._&m5.=1;
else IF PCT_X&m6._&m5.<-50% then  Ind_X&m6._&m5.=-1;
else  Ind_X&m6._&m5.=0;


IF PCT_X&m7._&m6.>50% then Ind_X&m7._&m6.=1;
else IF PCT_X&m7._&m6.<-50% then  Ind_X&m7._&m6.=-1;
else  Ind_X&m7._&m6.=0;


IF PCT_X&m8._&m7.>50% then Ind_X&m8._&m7.=1;
else IF PCT_X&m8._&m7.<-50% then  Ind_X&m8._&m7.=-1;
else  Ind_X&m8._&m7.=0;


IF PCT_X&m9._&m8.>50% then Ind_X&m9._&m8.=1;
else IF PCT_X&m9._&m8.<-50% then  Ind_X&m9._&m8.=-1;
else  Ind_X&m9._&m8.=0;	

IF PCT_X&m10._&m9.>50% then Ind_X&m10._&m9.=1;
else IF PCT_X&m10._&m9.<-50% then  Ind_X&m10._&m9.=-1;
else  Ind_X&m10._&m9.=0;	

IF PCT_X&m11._&m10.>50% then Ind_X&m11._&m10.=1;
else IF PCT_X&m11._&m10.<-50% then  Ind_X&m11._&m10.=-1;
else  Ind_X&m11._&m10.=0;	
				 

/*Part3*/
Total_increases_More50PCT=SUM( (Ind_X&m1._&m0.=1),
( Ind_X&m2._&m1.=1),
( Ind_X&m3._&m2.=1), 
( Ind_X&m4._&m3.=1),
( Ind_X&m5._&m4.=1), 
(Ind_X&m6._&m5.=1),
(Ind_X&m7._&m6.=1),
( Ind_X&m8._&m7.=1),
( Ind_X&m9._&m8.=1),
( Ind_X&m10._&m9.=1),
( Ind_X&m11._&m10.=1));

Total_Decreases_More50PCT=SUM( (Ind_X&m1._&m0.=-1),
( Ind_X&m2._&m1.=-1),
( Ind_X&m3._&m2.=-1), 
( Ind_X&m4._&m3.=-1),
( Ind_X&m5._&m4.=-1),
 (Ind_X&m6._&m5.=-1), 
(Ind_X&m7._&m6.=-1),
( Ind_X&m8._&m7.=-1),
( Ind_X&m9._&m8.=-1),
( Ind_X&m10._&m9.=-1),
( Ind_X&m11._&m10.=-1));

Total_NoChanges_MoreLess50PCT=SUM( (Ind_X&m1._&m0.=0),
( Ind_X&m2._&m1.=0),
( Ind_X&m3._&m2.=0), 
( Ind_X&m4._&m3.=0),
( Ind_X&m5._&m4.=0),
 (Ind_X&m6._&m5.=0),
 (Ind_X&m7._&m6.=0),
( Ind_X&m8._&m7.=0),
( Ind_X&m9._&m8.=0),
( Ind_X&m10._&m9.=0),
( Ind_X&m11._&m10.=0));

Re: Clever way to write a long code of new varaibles

Reeza — Sat, 02 Nov 2019 17:34:23 GMT

Use arrays instead.

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

@DaveStar wrote:

Hello

I need to run a dynamic report every week.

In the report there are 12 user defined macro variables (m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11).

For each customer there is one row with 13 fields:

X_&m0

X_&m1

X_&m2

X_&m3

X_&m4

X_&m5

X_&m6

X_&m7

X_&m8

X_&m9

X_&m10

X_&m11

In the code there are 3 parts:

part1- calculate % change in var X between each consecutive months

part2-For each % change need to build indicator if there is %increase higher than 50%,or %decrease higher than 50%,or other.

Part3-Need to calculate How many increases in more than 50% occurred and How many decreases in more than 50% occurred

My question:

Is there a way to write this long code by a more clever way?

Please note that by writing a long code as I did there might be typing errors

/*part1- Find % change of X between each  2 consecutive  months*/
PCT_X&m1._&m0.=(X_&m1.-X_&m0.)/X_m0. ;				
PCT_X&m2._&m1.=(X_&m2.-X_&m1.)/X_m1. ;				
PCT_X&m3._&m2.=(X_&m3.-X_&m2.)/X_m2. ;				
PCT_X&m4._&m3.=(X_&m4.-X_&m3.)/X_m3. ;				
PCT_X&m5._&m4.=(X_&m5.-X_&m4.)/X_m4. ;				
PCT_X&m6._&m5.=(X_&m6.-X_&m5.)/X_m5. ;				
PCT_X&m7._&m6.=(X_&m7.-X_&m6.)/X_m6. ;				
PCT_X&m8._&m7.=(X_&m8.-X_&m7.)/X_m7. ;				
PCT_X&m9._&m8.=(X_&m9.-X_&m8.)/X_m8. ;				
PCT_X&m10._&m9.=(X_&m10.-X_&m9.)/X_m9. ;			
PCT_X&m11._&m10.=(X_&m11.-X_&m10.)/X_m10. ;	

/*Part2*/
IF PCT_X&m1._&m0.>50% then Ind_X&m1._&m0.=1;
else IF PCT_X&m1._&m0.<-50% then  Ind_X&m1._&m0.=-1;
else  Ind_X&m1._&m0.=0;

IF PCT_X&m2._&m1.>50% then Ind_X&m2._&m1.=1;
else IF PCT_X&m2._&m1.<-50% then  Ind_X&m2._&m1.=-1;
else  Ind_X&m2._&m1.=0;

IF PCT_X&m3._&m2.>50% then Ind_X&m3._&m2.=1;
else IF PCT_X&m3._&m2.<-50% then  Ind_X&m3._&m2.=-1;
else  Ind_X&m3._&m2.=0;

IF PCT_X&m4._&m3.>50% then Ind_X&m4._&m3.=1;
else IF PCT_X&m4._&m3.<-50% then  Ind_X&m4._&m3.=-1;
else  Ind_X&m4._&m3.=0;


IF PCT_X&m5._&m4.>50% then Ind_X&m5._&m4.=1;
else IF PCT_X&m5._&m4.<-50% then  Ind_X&m5._&m4.=-1;
else  Ind_X&m5._&m4.=0;
	

IF PCT_X&m6._&m5.>50% then Ind_X&m6._&m5.=1;
else IF PCT_X&m6._&m5.<-50% then  Ind_X&m6._&m5.=-1;
else  Ind_X&m6._&m5.=0;


IF PCT_X&m7._&m6.>50% then Ind_X&m7._&m6.=1;
else IF PCT_X&m7._&m6.<-50% then  Ind_X&m7._&m6.=-1;
else  Ind_X&m7._&m6.=0;


IF PCT_X&m8._&m7.>50% then Ind_X&m8._&m7.=1;
else IF PCT_X&m8._&m7.<-50% then  Ind_X&m8._&m7.=-1;
else  Ind_X&m8._&m7.=0;


IF PCT_X&m9._&m8.>50% then Ind_X&m9._&m8.=1;
else IF PCT_X&m9._&m8.<-50% then  Ind_X&m9._&m8.=-1;
else  Ind_X&m9._&m8.=0;	

IF PCT_X&m10._&m9.>50% then Ind_X&m10._&m9.=1;
else IF PCT_X&m10._&m9.<-50% then  Ind_X&m10._&m9.=-1;
else  Ind_X&m10._&m9.=0;	

IF PCT_X&m11._&m10.>50% then Ind_X&m11._&m10.=1;
else IF PCT_X&m11._&m10.<-50% then  Ind_X&m11._&m10.=-1;
else  Ind_X&m11._&m10.=0;	
				 

/*Part3*/
Total_increases_More50PCT=SUM( (Ind_X&m1._&m0.=1),
( Ind_X&m2._&m1.=1),
( Ind_X&m3._&m2.=1), 
( Ind_X&m4._&m3.=1),
( Ind_X&m5._&m4.=1), 
(Ind_X&m6._&m5.=1),
(Ind_X&m7._&m6.=1),
( Ind_X&m8._&m7.=1),
( Ind_X&m9._&m8.=1),
( Ind_X&m10._&m9.=1),
( Ind_X&m11._&m10.=1));

Total_Decreases_More50PCT=SUM( (Ind_X&m1._&m0.=-1),
( Ind_X&m2._&m1.=-1),
( Ind_X&m3._&m2.=-1), 
( Ind_X&m4._&m3.=-1),
( Ind_X&m5._&m4.=-1),
 (Ind_X&m6._&m5.=-1), 
(Ind_X&m7._&m6.=-1),
( Ind_X&m8._&m7.=-1),
( Ind_X&m9._&m8.=-1),
( Ind_X&m10._&m9.=-1),
( Ind_X&m11._&m10.=-1));

Total_NoChanges_MoreLess50PCT=SUM( (Ind_X&m1._&m0.=0),
( Ind_X&m2._&m1.=0),
( Ind_X&m3._&m2.=0), 
( Ind_X&m4._&m3.=0),
( Ind_X&m5._&m4.=0),
 (Ind_X&m6._&m5.=0),
 (Ind_X&m7._&m6.=0),
( Ind_X&m8._&m7.=0),
( Ind_X&m9._&m8.=0),
( Ind_X&m10._&m9.=0),
( Ind_X&m11._&m10.=0));

Re: Clever way to write a long code of new varaibles

DaveStar — Sat, 02 Nov 2019 21:29:37 GMT

I am trying to use array ,for example for first step.

I know that it is not working well because SAS doesn't know what is Mj-1.

The help I need is related to using of j-1 element

array PCT_X(11)  PCT_&m1._&m0.  
PCT_&m2._&m1.     PCT_&m3._&m2.
PCT_&m4._&m3.     PCT_&m5._&m4. 
PCT_&m6._&m5.     PCT_&m7._&m6.  
PCT_&m8._&m7.    PCT_&m9._&m8.
 PCT_&m10._&m9.    PCT_&m11._&m10.;																		
do j = 1 to 12;																		
PCT_X(j)=(X_&mj.-X_&mj-1.)/X_mj-1. ;
end;

Re: Clever way to write a long code of new varaibles

Tom — Sun, 03 Nov 2019 02:08:46 GMT

Why do you have so many MACRO variables?

If you want to reference 12 regular variables with numeric suffix you can just use a variable list.

array x x1-x12 ;

If you want make the min and max number variable you just need TWO macro variables.

%let first_month=1;
%let last_month=12;
....
array x x&first_month-x&last_month;

Re: Clever way to write a long code of new varaibles

Reeza — Sun, 03 Nov 2019 07:11:33 GMT

That’s not an array, it has one variable.
I strongly suspect you’re making this a harder problem than it is and suspect you need to restructure your data as well. Given what you’ve shown it’s hard to say, but it would be really helpful if you could add some context and explanation behind what you’re trying to do.

Basically this seems like an XY problem.

Re: Clever way to write a long code of new varaibles

Reeza — Sun, 03 Nov 2019 07:12:32 GMT

Yup, rereading your question it’s your data structure that’s the issue. Flip your data to a long format and this becomes an incredibly trivial calculation.

Re: Clever way to write a long code of new varaibles

Kurt_Bremser — Sun, 03 Nov 2019 13:41:27 GMT

Maxims 19 and 33. Transpose to long format, and the code will become simple.

After transposing, use the lag() function to compare with the previous month, and use retained counters.

Re: Clever way to write a long code of new varaibles

Reeza — Sun, 03 Nov 2019 18:02:24 GMT

If you transpose your data to a long format, your code becomes something as follows. This is based on the SASHELP.STOCKS data set which has monthly data, similar to your structure. You can run the following to see.

data stocks;
set sashelp.stocks;
by stock;
length change_category $20.;

change = open/lag(open) - 1;
if first.stock then call missing(change);
else do;
   if change => 0.5 then change_category = "Greater than 50%";
   else if change <= -0.5 then change_category = "Less than 50%";
   else if change <0.5 and change >-0.5 then change_category = "Between -50% and 50%";
end;

run;

proc freq data=stocks;
table stock*change_category;
table change_category;
run;

If you want to retain this structure for whatever reason, I would recommend transposing to long, doing your calculations and then transposing back to a wide format again. This leaves your code dynamic and easy to adapt for changes to the amount of months or the addition of new records or a new metric/rule.

Here are tutorials on transposing your data from long to wide and wide to long.

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd