Hello For each customer I have one row with 12 columns of values per month (month 1 is last month, month 2 is one month before last month, month 3 is two months before last month and so on). I want to calculate percentage change from one month to next month . So Need to calculate: PCT change from month 12 to month 11 (From value 90 to value  80  so it should be -0.11 calculated by (80-90)/90 ) PCT change from month 11 to month 10 PCT change from month 10 to month 9 PCT change from month 9 to month 8 PCT change from month 8  to month 7 PCT change from month 7 to month 6 PCT change from month 6 to month 5 PCT change from month 5 to month 4 PCT change from month 4 to month 3 PCT change from month 3 to month 2 PCT change from month 2 to month 1   However, My code doesn't calculate it well!! for example: PCT_change_12_To_11 should be  -0.11 but it shows 0.125 What is wrong in my code???   Data have; input Rev12 Rev11 Rev10 Rev9 Rev8 Rev7 Rev6 Rev5 Rev4 Rev3 Rev2 Rev1; cards; 90 80 85 89 94 90 78 75 85 -9 24 32 ; Run; data want(drop=i); set have; array AAA{*} Rev12 Rev11 Rev10 Rev9 Rev8 Rev7 Rev6 Rev5 Rev4 Rev3 Rev2 Rev1; array PCT_change{*} PCT_change_12_To_11 PCT_change_11_To_10 PCT_change_10_To_9 PCT_change_9_To_8 PCT_change_8_To_7 PCT_change_7_To_6 PCT_change_6_To_5 PCT_change_5_To_4 PCT_change_4_To_3 PCT_change_3_To_2 PCT_change_2_To_1; do i=1 to dim(AAA)-1; if AAA[i+1] not in (.,0) then PCT_change[i]=(AAA[i]-AAA[i+1])/AAA[i+1]; end; run;     ... View more

Hello I have a data set with wide structure  and each customer id has one row with multiple columns of RevRatio (Each column represent another month. Month1 is last month, month 2 is one month before last month and so on) I want to calculate the change in PCT between each month and next month and also calculate indicators. IS there another way to calculate it instead of the macro or the macro I wrote is the best way? Data have; input CustID RevRatio1-RevRatio12; cards; 1 0.9 0.8 0.8 0.7 0.9 0.9 0.7 0.6 0.9 0.9 0.7 0.6 2 0.6 0.1 0.2 0.3 0.2 0.4 0.5 1.1 0.9 0.7 0.8 0.9 3 -9 -9 -9 -9 -9 -9 -9 -9 -9 -9 -9 -9 4 -9 0.2 0.1 0.4 0.5 -9 -8 0.1 0.2 0.3 0.4 0.1 ; Run; /*********** Way1-write it without macro****************/ /*********** Way1-write it without macro****************/ /*********** Way1-write it without macro****************/ %let Ratio=RevRatio; Data want; set have; IF &Ratio.2 not in (0,-6,-7,-8,-9,.) and &Ratio.1 not in (-6,-7,-8,-9,.) then PCT_change_2_1=(&Ratio.1-&Ratio.2)/&Ratio.2; IF &Ratio.3 not in (0,-6,-7,-8,-9,.) and &Ratio.2 not in (-6,-7,-8,-9,.) then PCT_change_3_2=(&Ratio.2-&Ratio.3)/&Ratio.3; IF &Ratio.4 not in (0,-6,-7,-8,-9,.) and &Ratio.3 not in (-6,-7,-8,-9,.) then PCT_change_4_3=(&Ratio.3-&Ratio.4)/&Ratio.4; IF &Ratio.5 not in (0,-6,-7,-8,-9,.) and &Ratio.4 not in (-6,-7,-8,-9,.) then PCT_change_5_4=(&Ratio.4-&Ratio.5)/&Ratio.5; IF &Ratio.6 not in (0,-6,-7,-8,-9,.) and &Ratio.5 not in (-6,-7,-8,-9,.) then PCT_change_6_5=(&Ratio.5-&Ratio.6)/&Ratio.6; IF &Ratio.7 not in (0,-6,-7,-8,-9,.) and &Ratio.6 not in (-6,-7,-8,-9,.) then PCT_change_7_6=(&Ratio.6-&Ratio.7)/&Ratio.7; IF &Ratio.8 not in (0,-6,-7,-8,-9,.) and &Ratio.7 not in (-6,-7,-8,-9,.) then PCT_change_8_7=(&Ratio.7-&Ratio.8)/&Ratio.8; IF &Ratio.9 not in (0,-6,-7,-8,-9,.) and &Ratio.8 not in (-6,-7,-8,-9,.) then PCT_change_9_8=(&Ratio.8-&Ratio.9)/&Ratio.9; IF &Ratio.10 not in (0,-6,-7,-8,-9,.) and &Ratio.9 not in (-6,-7,-8,-9,.) then PCT_change_10_9=(&Ratio.9-&Ratio.10)/&Ratio.10; IF &Ratio.11 not in (0,-6,-7,-8,-9,.) and &Ratio.10 not in (-6,-7,-8,-9,.) then PCT_change_11_10=(&Ratio.10-&Ratio.11)/&Ratio.11; IF &Ratio.12 not in (0,-6,-7,-8,-9,.) and &Ratio.11 not in (-6,-7,-8,-9,.) then PCT_change_12_11=(&Ratio.11-&Ratio.12)/&Ratio.12; IF PCT_change_2_1>0.05 AND &Ratio.1>0.5 then Ind_2_1=1; /****השינוי מחודש קודם גדול מ5% והיחס בחודש יעד גדול מ50%****/ else IF &Ratio.1>1 and &Ratio.2<1 then Ind_2_1=1;/***מעבר ממצב של לא חורג למצב של חורג***/ else IF &Ratio.2 ne -9 and &Ratio.1 = -9 then Ind_2_1=1; /***בחודש יעד מנצל לא מסגרת ובחודש קודם במצב אחר****/ else IF &Ratio.1>0.5 AND &Ratio.2 in (0,-8,-9) then Ind_2_1=1; /***יחס בחודש יעד גדול מ50% ובחודש קודם יחס היה 0 או מינוס 99999,99998***/ else Ind_2_1=0; IF PCT_change_3_2>0.05 AND &Ratio.2>0.5 then Ind_3_2=1; else IF &Ratio.2>1 and &Ratio.3<1 then Ind_3_2=1; else IF &Ratio.3 ne -9 and &Ratio.2 = -9 then Ind_3_2=1; else IF &Ratio.2>0.5 AND &Ratio.3 in (0,-8,-9) then Ind_3_2=1; else Ind_3_2=0; IF PCT_change_4_3>0.05 AND &Ratio.3>0.5 then Ind_4_3=1; else IF &Ratio.3>1 and &Ratio.4<1 then Ind_4_3=1; else IF &Ratio.4 ne -9 and &Ratio.3 = -9 then Ind_4_3=1; else IF &Ratio.3>0.5 AND &Ratio.4 in (0,-8,-9) then Ind_4_3=1; else Ind_4_3=0; IF PCT_change_5_4>0.05 AND &Ratio.4>0.5 then Ind_5_4=1; else IF &Ratio.4>1 and &Ratio.5<1 then Ind_5_4=1; else IF &Ratio.5 ne -9 and &Ratio.4 = -9 then Ind_5_4=1; else IF &Ratio.4>0.5 AND &Ratio.5 in (0,-8,-9) then Ind_5_4=1; else Ind_5_4=0; IF PCT_change_6_5>0.05 AND &Ratio.5>0.5 then Ind_6_5=1; else IF &Ratio.5>1 and &Ratio.6<1 then Ind_6_5=1; else IF &Ratio.6 ne -9 and &Ratio.5 = -9 then Ind_6_5=1; else IF &Ratio.5>0.5 AND &Ratio.6 in (0,-8,-9) then Ind_6_5=1; else Ind_6_5=0; IF PCT_change_7_6>0.05 AND &Ratio.6>0.5 then Ind_7_6=1; else IF &Ratio.6>1 and &Ratio.7<1 then Ind_7_6=1; else IF &Ratio.7 ne -9 and &Ratio.6 = -9 then Ind_7_6=1; else IF &Ratio.6>0.5 AND &Ratio.7 in (0,-8,-9) then Ind_7_6=1; else Ind_7_6=0; IF PCT_change_8_7>0.05 AND &Ratio.7>0.5 then Ind_8_7=1; else IF &Ratio.7>1 and &Ratio.8<1 then Ind_8_7=1; else IF &Ratio.8 ne -9 and &Ratio.7 = -9 then Ind_8_7=1; else IF &Ratio.7>0.5 AND &Ratio.8 in (0,-8,-9) then Ind_8_7=1; else Ind_8_7=0; IF PCT_change_9_8>0.05 AND &Ratio.8>0.5 then Ind_9_8=1; else IF &Ratio.8>1 and &Ratio.9<1 then Ind_9_8=1; else IF &Ratio.9 ne -9 and &Ratio.8 = -9 then Ind_9_8=1; else IF &Ratio.8>0.5 AND &Ratio.9 in (0,-8,-9) then Ind_9_8=1; else Ind_9_8=0; IF PCT_change_10_9>0.05 AND &Ratio.9>0.5 then Ind_10_9=1; else IF &Ratio.9>1 and &Ratio.10<1 then Ind_10_9=1; else IF &Ratio.10 ne -9 and &Ratio.9 = -9 then Ind_10_9=1; else IF &Ratio.9>0.5 AND &Ratio.10 in (0,-8,-9) then Ind_10_9=1; else Ind_10_9=0; IF PCT_change_11_10>0.05 AND &Ratio.10>0.5 then Ind_11_10=1; else IF &Ratio.10>1 and &Ratio.11<1 then Ind_11_10=1; else IF &Ratio.11 ne -9 and &Ratio.10 = -9 then Ind_11_10=1; else IF &Ratio.10>0.5 AND &Ratio.11 in (0,-8,-9) then Ind_11_10=1; else Ind_11_10=0; IF PCT_change_12_11>0.05 AND &Ratio.11>0.5 then Ind_12_11=1; else IF &Ratio.11>1 and &Ratio.12<1 then Ind_12_11=1; else IF &Ratio.12 ne -9 and &Ratio.11 = -9 then Ind_12_11=1; else IF &Ratio.11>0.5 AND &Ratio.12 in (0,-8,-9) then Ind_12_11=1; else Ind_12_11=0; Run; /***********Way2-Try to write it in macro code****************/ /***********Way2-Try to write it in macro code****************/ /***********Way2-Try to write it in macro code****************/ /***********Way2-Try to write it in macro code****************/ Data want2; set have; %macro RRR(Ratio,from,To); IF &Ratio.2 not in (0,-6,-7,-8,-9,.) and &Ratio.1 not in (-6,-7,-8,-9,.) then PCT_change_&From._&To.=(&Ratio.1-&Ratio.2)/&Ratio.2; IF PCT_change_&From._&To.>0.05 AND &Ratio.1>0.5 then Ind_&From._&To.=1; /****השינוי מחודש קודם גדול מ5% והיחס בחודש יעד גדול מ50%****/ else IF &Ratio.&To.>1 and &Ratio.&From.<1 then Ind_&From._&To.=1; /***מעבר ממצב של לא חורג למצב של חורג***/ else IF &Ratio.&From. ne -9 and &Ratio.&To. = -9 then Ind_&From._&To.=1; /***בחודש יעד מנצל לא מסגרת ובחודש קודם במצב אחר****/ else IF &Ratio.&To.>0.5 AND &Ratio.&From. in (0,-8,-9) then Ind_&From._&To.=1; /***יחס בחודש יעד גדול מ50% ובחודש קודם יחס היה 0 או מינוס 99999,99998***/ else Ind_&From._&To.=0; %mend RRR; %RRR(Ratio=RevRatio,from=2,To=1) %RRR(Ratio=RevRatio,from=3,To=2) %RRR(Ratio=RevRatio,from=4,To=3) %RRR(Ratio=RevRatio,from=5,To=4) %RRR(Ratio=RevRatio,from=6,To=5) %RRR(Ratio=RevRatio,from=7,To=6) %RRR(Ratio=RevRatio,from=8,To=7) %RRR(Ratio=RevRatio,from=9,To=8) %RRR(Ratio=RevRatio,from=10,To=9) %RRR(Ratio=RevRatio,from=11,To=10) %RRR(Ratio=RevRatio,from=12,To=11) Run; ... View more

Hello I have a data set with multiple rows for each customerID. Each row represent data on other month. I want to find the last month (The closest month to current month) that Customer moved from  status 0 to status 1. For example: For customer 123456 2024-01    0 2024-02   0 2024-03   1 2024-04  0  2024-05  0 2024-06  0 2024-07  0 2024-08 1 2024-09 0 2024-10 0 2024-11 0 You can see that last month of change from status 0 to 1 is 2024-08 What is the way to find it by code?   Data have; input CustID status 123 202401 0 123 202402 0 123 202403 1 123 202404 1 123 202405 0 123 202406 0 123 202407 1 123 202408 0 123 202409 0 123 202410 0 123 20411 0 ; Run;     ... View more

Hello Foe each customer I have a data set with wealth value and also values of percentiles that were calculated from all data. My purpose- For each customer find to which percentile the value wealth belong. For example: wealth 300 belong to P90 wealth 50 belong to P20 What is the way to classify each value to percentile group?       Data have; input CustID wealth P5 P10 P20 P30 P40 P50 P60 P70 P80 P90 P95 ; cards; 1 300 20 40 70 90 120 150 200 230 280 400 500 2 50 20 40 70 90 120 150 200 230 280 400 500 ; Run;     ... View more

Hello Let's say that I have a data set with 3 columns: rank,min,max(Note- this table was created by proc rank) I wanted to calculated percentiles 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25 But in the output i didn't get all the percentiles I have asked. My question; What is the way to add rows of percentiles that doesn't exist and  they will get values of columns Min,MAX from the upper row. For example: There is no row for P6  so we will add row with P6 and it  will get value of min and max from row P5   There is no row fro P19 till P24 so we will add these rows and min,max values will come from row P18    What is the way to add the missing rows?     Data have; infile datalines dlm=','; input rank min max; cards; 0, 0,800 1,1000,1901 2,2000,2425 3,2500,3000 4,3001,4000 5,4015,4935 7,5000,5000 9,5001,6000 10,6001,6480 13,6500,6500 16,6501,6934 17,7000,7200 18,7244,7400 25,7500,7500 ; Run; Data want; infile datalines dlm=','; input rank min max; cards; 0, 0,800 1,1000,1901 2,2000,2425 3,2500,3000 4,3001,4000 5,4015,4935 6,4015,4935 7,5000,5000 8,5000,5000 9,5001,6000 10,6001,6480 11,6001,6480 12,6001,6480 13,6500,6500 14,6500,6500 15,6500,6500 16,6501,6934 17,7000,7200 18,7244,7400 19,7244,7400 20,7244,7400 21,7244,7400 22,7244,7400 23,7244,7400 24,7244,7400 25,7500,7500 ; Run;      ... View more

Hello I want to calculate following percentiles and then classify each row into a group. I want to define groups (Ranks) based on following percentiles- P5,P10,P20,P30,P40,P50,P60,P70,P80,P90,P95   The following code  create the percentiles-P10,P20,P30,P40,P50,P60,P70,P80,P90   but no P5,P95 What is the way to add them??? proc rank data=sashelp.cars out=have groups=10; var invoice; ranks rank; run; proc sql; create table rank_groups_Definition as select rank, min(invoice) as min, max(invoice) as max from have group by rank ; quit; data rank_groups_Definition_2; set rank_groups_Definition; _MIN_=lag(max)+1; IF _MIN_=. then _MIN_=0; drop min; rename _min_=min; Run; proc sql; create table want as select a.*,b.rank from sashelp.cars as a left join rank_groups_Definition_2 as b on b.Min<=a.invoice<=b.Max ; quit; ... View more