Finding consecutive decreases

[LaiQ]

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!

[RW9]

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.

[LaiQ]

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

[Haikuo · Accepted solution]

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;

[LaiQ]

Thank you! I learn something new today! Will go read up on DoW LOOP!

[Ksharp]

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;

[LaiQ]

I see, will figure out using PROC SUMMARY. Thank you!