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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.