BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LaiQ
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
Ksharp
Super User

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;

LaiQ
Calcite | Level 5
Thank you!
Astounding
PROC Star

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;

Astounding
PROC Star

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;

LaiQ
Calcite | Level 5
Noted, will look into the codes. Thank you!
PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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
LaiQ
Calcite | Level 5
Thank you so much! Appreciate your help!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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