DATA Step, Macro, Functions and more

Total consecutive decreases

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

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
Respected Advisor
Posts: 4,663

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

View solution in original post


All Replies
Super User
Posts: 9,691

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

Re: Total consecutive decreases

Thank you!
Super User
Posts: 5,099

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: 5,099

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
Respected Advisor
Posts: 4,663

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
Respected Advisor
Posts: 4,663

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.

Need further help from the community? Please ask a new question.

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