DATA Step, Macro, Functions and more

Finding consecutive decreases

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

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
Respected Advisor
Posts: 3,124

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;

 

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

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
Respected Advisor
Posts: 3,124

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: 9,687

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.

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

Discussion stats
  • 6 replies
  • 280 views
  • 0 likes
  • 4 in conversation