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));
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));
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;
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;
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: