Solved
Contributor
Posts: 31

# Total consecutive decreases

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.

Accepted Solutions
Solution
‎09-06-2016 10:56 PM
Posts: 5,524

## Re: Total consecutive decreases

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;
``````
PG

All Replies
Super User
Posts: 10,770

## Re: Total consecutive decreases

```
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;

```
Contributor
Posts: 31

Thank you!
Super User
Posts: 6,762

## Re: Total consecutive decreases

[ Edited ]

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;

Super User
Posts: 6,762

## Re: Total consecutive decreases

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;

Contributor
Posts: 31

## Re: Total consecutive decreases

Noted, will look into the codes. Thank you!
Solution
‎09-06-2016 10:56 PM
Posts: 5,524

## Re: Total consecutive decreases

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;
``````
PG
Posts: 5,524

## Re: Total consecutive decreases

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;
``````
PG
Contributor
Posts: 31

## Re: Total consecutive decreases

Thank you so much! Appreciate your help!
☑ This topic is solved.

Discussion stats
• 8 replies
• 487 views
• 1 like
• 4 in conversation