Hi all,
I need to find the number of months with consecutive decreases, which is 5 in this case (3 months then 2 months), ignoring those which are not consecutive:
ID month sales lag(sales) decreasecount (1 if lag(sales) > sales) 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 1 Jul 90 110 1 1 Aug 150 90 0 1 Sep 100 150 1 1 Oct 90 100 1 1 Nov 110 90 0 1 Dec 100 110 1
Appreciate any help and thank you for the time.
You only need basic data:
data have;
input ID month $ sales;
cards;
1 Jan 100
1 Feb 120
1 Mar 90
1 Apr 80
1 May 70
1 Jun 110
1 Jul 90
1 Aug 150
1 Sep 100
1 Oct 90
1 Nov 110
1 Dec 100
;
data want;
decreases = 0;
consecutive = 0;
do until(last.id);
set have; by id;
if previousSales > sales then do;
consecutive + 1;
decreases + (consecutive>1) + (consecutive=2);
end;
else consecutive = 0;
previousSales = sales;
end;
keep id decreases;
run;
proc print noobs; var id decreases; run;
data have; input ID month $ sales lag 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 1 Jul 90 110 1 1 Aug 150 90 0 1 Sep 100 150 1 1 Oct 90 100 1 1 Nov 110 90 0 1 Dec 100 110 1 ; run; proc summary data=have; by id decreasecount notsorted; output out=temp; run; proc summary data=temp; where decreasecount=1 and _freq_ gt 1; by id; var _freq_; output out=want(drop=_:) sum=sum; run;
You might find this easier if you were to create DECREASECOUNT differently. Here's a one-step approach that keeps on incrementing the total count of decreases. So the final value is the only one that matters.
data want;
set have end=done;
if lag(sales) <= sales then new_decreasecount=0;
else new_decreasecount + 1;
if new_decreasecount > 1 then total_decreases_so_far + 1;
if done then put total_decreases_so_far=;
run;
Redo, using your existing data set:
data want;
set have end=done;
by decreasecount notsorted;
if decreasecount=1 and first.decreasecount=0 or last.decreasecount=0 then total_decrease + 1;
if done then put total_decrease=;
run;
You only need basic data:
data have;
input ID month $ sales;
cards;
1 Jan 100
1 Feb 120
1 Mar 90
1 Apr 80
1 May 70
1 Jun 110
1 Jul 90
1 Aug 150
1 Sep 100
1 Oct 90
1 Nov 110
1 Dec 100
;
data want;
decreases = 0;
consecutive = 0;
do until(last.id);
set have; by id;
if previousSales > sales then do;
consecutive + 1;
decreases + (consecutive>1) + (consecutive=2);
end;
else consecutive = 0;
previousSales = sales;
end;
keep id decreases;
run;
proc print noobs; var id decreases; run;
Here is a commented version of the code.
data want;
/* Initialize the counts before each ID */
decreases = 0;
consecutive = 0;
/* Loop over all observation in an ID group */
do until(last.id);
/* Read obs, create automatic variable last.id */
set have; by id;
/* previousSales is given a value at the end of the loop. On the first
iteration, previousSales is missing (-Infinity) and the comparison
is false */
if previousSales > sales then do;
consecutive + 1; /* Count number of consecutive decreases */
/* As the number of consecutive decreases goes up, the total count
is increased following the sequence 0, 2, 1, 1, 1... created by
the sum of two logical expressions. When consecutive = 1, both
conditions are false (0+0). When consecutive = 2, both conditions
are true (1+1). When consecutive > 2 only the first logical
condition is true (1+0). */
decreases + (consecutive>1) + (consecutive=2);
end;
else consecutive = 0; /* Reset consecutive count */
previousSales = sales; /* Remember for next loop iteration */
end;
keep id decreases;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.