topic Re: Total consecutive decreases in SAS Programming

Total consecutive decreases

LaiQ — Mon, 05 Sep 2016 08:33:46 GMT

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.

Re: Total consecutive decreases

Ksharp — Mon, 05 Sep 2016 09:10:44 GMT


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;

Re: Total consecutive decreases

Astounding — Tue, 06 Sep 2016 01:05:48 GMT

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;

Re: Total consecutive decreases

Astounding — Tue, 06 Sep 2016 12:40:32 GMT

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;

Re: Total consecutive decreases

PGStats — Tue, 06 Sep 2016 17:32:23 GMT

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;

Re: Total consecutive decreases

PGStats — Wed, 07 Sep 2016 03:24:39 GMT

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;

Re: Total consecutive decreases

LaiQ — Wed, 07 Sep 2016 03:27:54 GMT

Thank you so much! Appreciate your help!

Re: Total consecutive decreases

LaiQ — Wed, 07 Sep 2016 03:28:14 GMT

Thank you!

Re: Total consecutive decreases

LaiQ — Wed, 07 Sep 2016 03:28:36 GMT

Noted, will look into the codes. Thank you!