topic Re: Finding consecutive decreases in SAS Programming

Finding consecutive decreases

LaiQ — Tue, 30 Aug 2016 11:51:21 GMT

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!

Re: Finding consecutive decreases

RW9 — Tue, 30 Aug 2016 11:58:41 GMT

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.

Re: Finding consecutive decreases

Haikuo — Tue, 30 Aug 2016 13:34:38 GMT

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;

Re: Finding consecutive decreases

Ksharp — Tue, 30 Aug 2016 13:40:05 GMT


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;

Re: Finding consecutive decreases

LaiQ — Wed, 31 Aug 2016 06:41:52 GMT

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

Re: Finding consecutive decreases

LaiQ — Wed, 31 Aug 2016 06:42:43 GMT

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

Re: Finding consecutive decreases

LaiQ — Wed, 31 Aug 2016 06:43:23 GMT

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