BookmarkSubscribeRSS Feed
DaveStar
Obsidian | Level 7

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

 

7 REPLIES 7
Reeza
Super User

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
Obsidian | Level 7

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
Super User
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
Super User Tom
Super User

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
Super User
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
Super User

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.

Reeza
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 7 replies
  • 653 views
  • 0 likes
  • 4 in conversation