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

DaveStar · Posted 11-02-2019 12:59 PM

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:

ID

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));

Reeza · Posted 11-02-2019 01:34 PM

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:

ID

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));

DaveStar · Posted 11-02-2019 05:07 PM

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;

Reeza · Posted 11-03-2019 02:11 AM

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.

Tom · Posted 11-02-2019 10:08 PM

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;

Reeza · Posted 11-03-2019 02:12 AM

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.

Kurt_Bremser · Posted 11-03-2019 08:41 AM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Reeza · Posted 11-03-2019 01:02 PM

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!