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!
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;
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.
Oh yes, I didn't think of it that way. Thank you!
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;
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;
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.
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.