BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LaiQ
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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
Calcite | Level 5
Thank you! I learn something new today! Will go read up on DoW LOOP!
Ksharp
Super User

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
Calcite | Level 5
I see, will figure out using PROC SUMMARY. Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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