DATA Step, Macro, Functions and more

How do I code consecutive change?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How do I code consecutive change?

I am doing a time series analysis for ticket created over time and would like to monitor consecutive concurrent decrease month over month for our clients. Below is my original data set:

 

Open Date

 

N of Clients

Sum of Tickets

01NOV14:00:00:00

1070

3530

01DEC14:00:00:00

1448

8486

01JAN15:00:00:00

1821

23580

01FEB15:00:00:00

1796

20648

01MAR15:00:00:00

1756

22872

01APR15:00:00:00

1789

21601

01MAY15:00:00:00

1746

20059

01JUN15:00:00:00

1757

12946

01JUL15:00:00:00

1780

22874

01AUG15:00:00:00

1799

24045

01SEP15:00:00:00

1855

27344

01OCT15:00:00:00

1777

23124

01NOV15:00:00:00

1682

17861

01DEC15:00:00:00

1868

19191

 

If there is a one month decrease in sum of tickets, then clients will receive a score of 0.9. If there are a two-month decrease in sum of tickets, then clients will receive a score of 0.7 and so on until the score is 0.1. When there is a one month increase in sum of tickets, the score should return to the prior level. The new data should look like this:

Open Date

 

 

N of Clients

Sum of Tickets

Consecutive

01NOV14:00:00:00

1070

3530

 

01DEC14:00:00:00

1448

8486

1

01JAN15:00:00:00

1821

23580

1

01FEB15:00:00:00

1796

20648

0.9

01MAR15:00:00:00

1756

22872

1

01APR15:00:00:00

1789

21601

0.9

01MAY15:00:00:00

1746

20059

0.7

01JUN15:00:00:00

1757

12946

0.5

01JUL15:00:00:00

1780

22874

0.7

01AUG15:00:00:00

1799

24045

0.9

01SEP15:00:00:00

1855

27344

1

01OCT15:00:00:00

1777

23124

0.9

01NOV15:00:00:00

1682

17861

0.7

01DEC15:00:00:00

1868

19191

0.9

 

I have tried different step by using do or LAG but can not code the consecutive score correct. My score always returns to 1 instead of the prior level when there is an increase. Can somebody help me on this programming? Thanks a lot!


Accepted Solutions
Solution
‎08-18-2016 12:13 PM
Super User
Posts: 19,855

Re: How do I code consecutive change?

I don't get the same values as you, but I also don't quite see your logic with your third record. 

 

Here's what I have, perhaps you can modify it to your logic?

 

data have;
informat date anydtdtm. sum total score 8.;
format date datetime21.;
input date sum total score @@;
cards;
01NOV14:00:00:00
1070
3530
0
01DEC14:00:00:00
1448
8486
1
01JAN15:00:00:00
1821
23580
1
01FEB15:00:00:00
1796
20648
0.9
01MAR15:00:00:00
1756
22872
1
01APR15:00:00:00
1789
21601
0.9
01MAY15:00:00:00
1746
20059
0.7
01JUN15:00:00:00
1757
12946
0.5
01JUL15:00:00:00
1780
22874
0.7
01AUG15:00:00:00
1799
24045
0.9
01SEP15:00:00:00
1855
27344
1
01OCT15:00:00:00
1777
23124
0.9
01NOV15:00:00:00
1682
17861
0.7
01DEC15:00:00:00
1868
19191
0.9
;
run;

proc format;
value order
1 = 1
2 = 0.9
3 = 0.7
4 = 0.5
5 = 0.3
6 = 0.1
;
run;


data want;
set have;
retain order 1;
lag_sum=lag(sum);
if _n_ ne 1 then do;
if sum>lag_sum then order=max(order-1, 1);
else order+1;
end;
value=put(order, order.);
run;

View solution in original post


All Replies
Super User
Posts: 19,855

Re: How do I code consecutive change?

Lag + retain are what you should be using. Can you post the code you tried?

Occasional Contributor
Posts: 9

Re: How do I code consecutive change?

[ Edited ]

Hi Reeza,

 

Thank you for your input. Yes, forgot to mention that I also used retain. Below please forgive my ugly code:


retain consecutive 1;
do consecutive= 1;
    if LAG(Sum_of_Tickets)>0;
    do consecutive= 0.9;
        if LAG(Sum_of_Tickets)<0;
        do consecutive= 0.7;
               if LAG(Sum_of_Tickets)<0;
               do consecutive= 0.5;
                   if LAG(Sum_of_Tickets)<0;
                   do consecutive= 0.3;
                       if LAG(Sum_of_Tickets)<0;
                       do consecutive= 0.1;
                       if LAG(Sum_of_Tickets)<0;
                       end;
                    end;
                 end;
             end;
         end;
      end;
run;

Solution
‎08-18-2016 12:13 PM
Super User
Posts: 19,855

Re: How do I code consecutive change?

I don't get the same values as you, but I also don't quite see your logic with your third record. 

 

Here's what I have, perhaps you can modify it to your logic?

 

data have;
informat date anydtdtm. sum total score 8.;
format date datetime21.;
input date sum total score @@;
cards;
01NOV14:00:00:00
1070
3530
0
01DEC14:00:00:00
1448
8486
1
01JAN15:00:00:00
1821
23580
1
01FEB15:00:00:00
1796
20648
0.9
01MAR15:00:00:00
1756
22872
1
01APR15:00:00:00
1789
21601
0.9
01MAY15:00:00:00
1746
20059
0.7
01JUN15:00:00:00
1757
12946
0.5
01JUL15:00:00:00
1780
22874
0.7
01AUG15:00:00:00
1799
24045
0.9
01SEP15:00:00:00
1855
27344
1
01OCT15:00:00:00
1777
23124
0.9
01NOV15:00:00:00
1682
17861
0.7
01DEC15:00:00:00
1868
19191
0.9
;
run;

proc format;
value order
1 = 1
2 = 0.9
3 = 0.7
4 = 0.5
5 = 0.3
6 = 0.1
;
run;


data want;
set have;
retain order 1;
lag_sum=lag(sum);
if _n_ ne 1 then do;
if sum>lag_sum then order=max(order-1, 1);
else order+1;
end;
value=put(order, order.);
run;
Occasional Contributor
Posts: 9

Re: How do I code consecutive change?

Hi Reeza, 

 

Thank you for your logic. Learnt a lot from it! 

Super User
Posts: 10,044

Re: How do I code consecutive change?

I love this question.



data have;
input date : $20. N Sum;
cards;
01NOV14:00:00:00
1070
3530
01DEC14:00:00:00
1448
8486
01JAN15:00:00:00
1821
23580
01FEB15:00:00:00
1796
20648
01MAR15:00:00:00
1756
22872
01APR15:00:00:00
1789
21601
01MAY15:00:00:00
1746
20059
01JUN15:00:00:00
1757
12946
01JUL15:00:00:00
1780
22874
01AUG15:00:00:00
1799
24045
01SEP15:00:00:00
1855
27344
01OCT15:00:00:00
1777
23124
01NOV15:00:00:00
1682
17861
01DEC15:00:00:00
1868
19191
;
run;
data temp;
 set have;
 sign=sign(dif(sum));
run;
data want;
 set temp;
 array x{6} _temporary_ (0.1 0.3 0.5 0.7 0.9 1);
 retain index 6;
 if _n_=1 then score=.;
  else do;
   index+sign;
   if index gt 6 then index=6;
    else if index lt 1 then index=1;
    
   score=x{index};
  end;
drop sign index;
run;


Occasional Contributor
Posts: 9

Re: How do I code consecutive change?

Thank you so much for your input. That works out very well!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 373 views
  • 0 likes
  • 3 in conversation