Solved
Contributor
Posts: 22

# 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: 10,787

## 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

All Replies
Super User
Posts: 10,787

## 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,

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

Posts: 3,167

## Re: Finding the starting month of decresing trend

Ksharp is on Beijing time,  he is likely asleep by now . 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,191

## 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

Posts: 3,167

## 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: 10,787

## 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: 10,787

## 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

Hi all,

regards

karthik

Posts: 5,543

## 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 and locked.

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