Solved
Contributor
Posts: 31

# Finding consecutive decreases

Hi all,

I am trying to find the number of months with consecutive decreases in sales.

For example,

ID Month Sales
1  Jan       100
1  Feb      120
1  Mar       90
1  Apr        80
1  May      70
1  Jun      110
2  Jan      150
2  Feb     150
2  Mar     100
2  Apr       90
2  May     110
2  Jun      100

Hence, I use LAG function to get the previous sales and assign 1 if it decreases and 0 if it remains or increases.

ID Month Sales PrevSales Decreasecount
1    Jan    100         .                0
1    Feb   120       100             0
1    Mar    90        120             1
1    Apr     80         90              1
1   May    70         80               1
1   Jun    110        70               0
2   Jan    150         .                 0
2   Feb   150       150              0
2   Mar   100       150              1
2   Apr     90       100               1
2   May   110       90                0
2   Jun    100      110               1

For ID 1, it should be 3 months.

And for ID 2, it should be 2 because it decreases consecutively then increases, then decreases again. Hence, ignoring the 2nd time it decreases.

If I sum the decreasecount, it might not be consecutive.

Hence, how can I find the number of months with consecutive decreases in sales only?

Appreciate if anyone can help!
Thank you!

Accepted Solutions
Solution
‎08-31-2016 02:42 AM
Posts: 3,167

## Re: Finding consecutive decreases

Given your intermediate result, alternatively we can leverage DOW, key word : DOW, notsorted.

``````data have;
input ID Month\$ Sales PrevSales Decreasecount;
cards;
1    Jan    100         .                0
1    Feb   120       100             0
1    Mar    90        120             1
1    Apr     80         90              1
1   May    70         80               1
1   Jun    110        70               0
2   Jan    150         .                 0
2   Feb   150       150              0
2   Mar   100       150              1
2   Apr     90       100               1
2   May   110       90                0
2   Jun    100      110               1
;

data want;
do _n_=1 by 1 until (last.Decreasecount);
set have;
by id Decreasecount notsorted;
end;

retain max_ct 0;

if Decreasecount=1 then
max_ct=max(_n_,max_ct);

if last.id then
do;
output;
max_ct=0;
end;

keep id max_ct;
run;``````

All Replies
Super User
Posts: 9,599

## Re: Finding consecutive decreases

Can you not, rather than setting to 1 or 0, just increment the value each time, with your current logic to get:

ID Month Sales PrevSales Decreasecount
1    Jan    100         .                0
1    Feb   120       100             0
1    Mar    90        120             1
1    Apr     80         90              2
1   May    70         80               3
1   Jun    110        70               0
2   Jan    150         .                 0
2   Feb   150       150              0
2   Mar   100       150              1
2   Apr     90       100               2
2   May   110       90                0
2   Jun    100      110               1

Then select the max() value from the list by ID will give you the correct count.

Contributor
Posts: 31

## Re: Finding consecutive decreases

Oh yes, I didn't think of it that way. Thank you!

Solution
‎08-31-2016 02:42 AM
Posts: 3,167

## Re: Finding consecutive decreases

Given your intermediate result, alternatively we can leverage DOW, key word : DOW, notsorted.

``````data have;
input ID Month\$ Sales PrevSales Decreasecount;
cards;
1    Jan    100         .                0
1    Feb   120       100             0
1    Mar    90        120             1
1    Apr     80         90              1
1   May    70         80               1
1   Jun    110        70               0
2   Jan    150         .                 0
2   Feb   150       150              0
2   Mar   100       150              1
2   Apr     90       100               1
2   May   110       90                0
2   Jun    100      110               1
;

data want;
do _n_=1 by 1 until (last.Decreasecount);
set have;
by id Decreasecount notsorted;
end;

retain max_ct 0;

if Decreasecount=1 then
max_ct=max(_n_,max_ct);

if last.id then
do;
output;
max_ct=0;
end;

keep id max_ct;
run;``````

Contributor
Posts: 31

## Re: Finding consecutive decreases

Thank you! I learn something new today! Will go read up on DoW LOOP!
Super User
Posts: 10,784

## Re: Finding consecutive decreases

```
data have;
input ID Month \$ Sales PrevSales Decreasecount;
cards;
1    Jan    100         .                0
1    Feb   120       100             0
1    Mar    90        120             1
1    Apr     80         90              1
1   May    70         80               1
1   Jun    110        70               0
2   Jan    150         .                 0
2   Feb   150       150              0
2   Mar   100       150              1
2   Apr     90       100               1
2   May   110       90                0
2   Jun    100      110               1
;
run;
proc summary data=have;
by id decreasecount notsorted;
output out=temp;
run;
proc summary data=temp(where=(decreasecount=1));
by id;
var _freq_;
output out=want max=freq;
run;

```
Contributor
Posts: 31

## Re: Finding consecutive decreases

I see, will figure out using PROC SUMMARY. Thank you!
☑ This topic is solved.