Finding the starting month of decresing trend

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Finding the starting month of decresing trend


Hello ,

I have 24months sales data from that i want to know from which month the sales are going down and became zero.

ex:

custid----- month1-month24

101------  100 200 300 500 10000 20000 500 400 300 200 100 0 0 0 0 0 0 0 0 0 0 0 0 0

i want  output as

custid ----decreasing_month

101---Month7(ie:500)

thanks..

regards

karthik


Accepted Solutions
Solution
‎04-23-2014 11:57 AM
Super User
Posts: 9,676

Re: Finding the starting month of decresing trend

Change the direction, and assuming there are no tie at the time declining to zero.

data x;
input custid month1-month24 ;
cards;
101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0
102  100 200 300 500 10000 20000 500 400 300 200 0 0 200 105 51 46 0 0 0 0 0 0 0 0
;
run;
data x;
 set x;
 array m{*} month1-month24;
 do i=dim(m) to 1 by -1;
  if m{i-1} lt m{i} then do; d=vname(m{i+1});leave;end;
 end;
 drop i;
run;

Xia Keshan

View solution in original post


All Replies
Super User
Posts: 9,676

Re: Finding the starting month of decresing trend

data x;
input custid month1-month24 ;
cards;
101  100 200 300 500 10000 20000 500 400 300 200 100 0 0 0 0 0 0 0 0 0 0 0 0 0
;
run;
data x;
 set x;
 array m{*} month1-month24;
 do i=2 to dim(m);
  if m{i-1} gt m{i} then do; d=vname(m{i});leave;end;
 end;
 drop i;
run;

Xia Keshan

Contributor
Posts: 22

Re: Finding the starting month of decresing trend

Hi Ksharp,

Thanks for your reply.

the above code is giving me the variable name of first time decreased  but i want

let the data is like below..
data x;

input custid month1-month24 ;

cards;

101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0

;

run;

In this case i want month14

thanks & regards

karthik

Respected Advisor
Posts: 3,124

Re: Finding the starting month of decresing trend

Ksharp is on Beijing time,  he is likely asleep by now Smiley Happy. So I will give my answer here:

data x;

input custid month1-month24 ;

cards;

101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0

;

run;

data want;

set x;

length d_mon $ 10;

array mon month:;

f=0;

do i=2 to dim(mon);

if mon(i) < mon(i-1) and f=0 then f=i;

else if mon(i) > mon(i-1) then f=0;

end;

d_mon=vname(mon(f));

keep custid d_mon;

run;

Haikuo

Valued Guide
Posts: 2,175

Re: Finding the starting month of decresing trend

how about using WHICHN() to locate the first zero in the array then work back toward the beginning identifying the required element where values start the decline toward that 0

Respected Advisor
Posts: 3,124

Re: Finding the starting month of decresing trend

It all depends on OP's intention. Apparently his plot thickens along the way. My solution is based on an assumption that OP's ultimate purpose is to capture the last wave.

Haikuo

Super User
Posts: 9,676

Re: Finding the starting month of decresing trend

Change the direction, and assuming there are no tie at the time declining to zero.

data x;
input custid month1-month24 ;
cards;
101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0
102  100 200 300 500 10000 20000 500 400 300 200 0 0 200 105 51 46 0 0 0 0 0 0 0 0
;
run;
data x;
 set x;
 array m{*} month1-month24;
 do i=dim(m) to 1 by -1;
  if m{i-1} lt m{i} then do; d=vname(m{i+1});leave;end;
 end;
 drop i;
run;

Xia Keshan

Solution
‎04-23-2014 11:57 AM
Super User
Posts: 9,676

Re: Finding the starting month of decresing trend

Change the direction, and assuming there are no tie at the time declining to zero.

data x;
input custid month1-month24 ;
cards;
101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0
102  100 200 300 500 10000 20000 500 400 300 200 0 0 200 105 51 46 0 0 0 0 0 0 0 0
;
run;
data x;
 set x;
 array m{*} month1-month24;
 do i=dim(m) to 1 by -1;
  if m{i-1} lt m{i} then do; d=vname(m{i+1});leave;end;
 end;
 drop i;
run;

Xia Keshan

Contributor
Posts: 22

Re: Finding the starting month of decresing trend

Hi all,

Thanks for your support......

regards

karthik

Respected Advisor
Posts: 4,646

Re: Finding the starting month of decresing trend

Assuming OP wants to identify all declines that end in sales=0 :

data have;

input custid month1-month24 ;

datalines;

101  100 200 300 500 10000 20000 500 400 300 200 100 10000 200000 105 51 46 0 0 0 0 0 0 0 0

102  100 200 300 500 10000 20000 500 400 300 200 0 0 200 105 51 46 0 0 0 0 0 0 0 0

;

%let nbMonths=24;

data want(keep=custId startMonth zeroMonth);

set have;

array m{0: &nbMonths.} dum month: (-1, &nbMonths.*0);

do zeroMonth = 2 to hbound(m);

  if m{zeroMonth} = 0 then do;

       do startMonth = zeroMonth to 1 by -1 while(m{startMonth} < m{startMonth-1}); end;

       if startMonth < zeroMonth then output;

       end;

  end;

run;

proc print data=want noobs; var custId startMonth zeroMonth; run;

                                       start    zero

                             custid    Month    Month

                                101       13       17

                                102        6       11

                                102       13       17

PG

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 326 views
  • 0 likes
  • 5 in conversation