cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
DaveStar
Obsidian | Level 7 DaveStar
Obsidian | Level 7

Clever way to write a long code of new varaibles

Posted 11-02-2019 12:59 PM (972 views)

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

 

0 Likes
Reply
7 REPLIES 7
Reeza
Super User Reeza
Super User

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

Posted 11-02-2019 01:34 PM (959 views)  |   In reply to DaveStar

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

 

 

0 Likes
Reply
DaveStar
Obsidian | Level 7 DaveStar
Obsidian | Level 7

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

Posted 11-02-2019 05:07 PM (926 views)  |   In reply to Reeza

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;

 

 

0 Likes
Reply
Reeza
Super User Reeza
Super User

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

Posted 11-03-2019 02:11 AM (882 views)  |   In reply to DaveStar
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.
0 Likes
Reply
Tom
Super User Tom
Super User

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

Posted 11-02-2019 10:08 PM (896 views)  |   In reply to DaveStar

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;
0 Likes
Reply
Reeza
Super User Reeza
Super User

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

Posted 11-03-2019 02:12 AM (882 views)  |   In reply to DaveStar
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.
0 Likes
Reply
Kurt_Bremser
Super User Kurt_Bremser
Super User

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

Posted 11-03-2019 08:41 AM (851 views)  |   In reply to DaveStar

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
0 Likes
Reply
Reeza
Super User Reeza
Super User

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

Posted 11-03-2019 01:02 PM (841 views)  |   In reply to DaveStar

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

0 Likes
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • ‎11-02-2019 12:59 PM
  • 973 views
  • 0 likes
  • 4 in conversation