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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: