DATA Step, Macro, Functions and more

Select increasing values over time

Reply
Frequent Contributor
Posts: 87

Select increasing values over time

[ Edited ]

 

Hi, I have data like the below. I need to check if the value field is increasing across months by product. I created the diff field so that if that value is positive in all observations in a group then its increasing.  

 

Please suggest how I can program to output only those products that are increasing in value over the months. 

 

Product month value diff
x 2016/04 104.94 0
x 2016/05 91.12 -14
x 2016/06 98 7
x 2016/07 117 19
x 2016/08 235.67 119
x 2016/09 118.89 -117
x 2016/10 136.67 18
y 2016/04 1.19 0
y 2016/05 1 0
y 2016/06 1.43 0
y 2016/07 0.89 -1
y 2016/08 0.78 0
y 2016/09 1.3 1
y 2016/10 3.5 2
z 2016/06 1 0
z 2016/10 2 1
Super User
Posts: 19,869

Re: sas base question

It's not clear exactly what you want as output. Based on your input what's your expected output. 

PROC Star
Posts: 766

Re: sas base question

If I understand you correctly, you want to output the observations at which diff > 0 ?

 

In that case simply use a subsetting if statement?

 

   data want;
      set have;
      if diff > 0;
   run;

 

 

Super User
Posts: 11,343

Re: sas base question

First make sure that your "month" variable is as SAS date valued variable.

Sort by product and month.

 

Proc data=have;

   by product;

   model value= month;

run;

 

If the slope (parameter estimate for MONTH variable) reported is greater than 0 the value is increasing.

 

Super User
Posts: 5,516

Re: sas base question

It's not clear whether DIFF=0 is acceptable or not, so I'll treat it as increasing.  It's takes a mildly more complex program to treat it as decreasing (because you need to ignore DIFF on the first observation for each PRODUCT).

 

data want;

keep_me = 'Y';

do until (last.product);

   set have;

   by product;

   if diff < 0 then keep_me='N';

end;

do until (last.product);

   set have;

   by product;

   if keep_me='Y' then output;

end;

drop keep_me;

run;

Super User
Posts: 7,857

Re: sas base question

[ Edited ]

I guess you'd need something like this:

data have;
input product $ month $ value diff;
cards;
x 	2016/04 	104.94 	0
x 	2016/05 	91.12 	-14
x 	2016/06 	98 	7
x 	2016/07 	117 	19
x 	2016/08 	235.67 	119
x 	2016/09 	118.89 	-117
x 	2016/10 	136.67 	18
y 	2016/04 	1.19 	0
y 	2016/05 	1 	0
y 	2016/06 	1.43 	0
y 	2016/07 	0.89 	-1
y 	2016/08 	0.78 	0
y 	2016/09 	1.3 	1
y 	2016/10 	3.5 	2
z 	2016/06 	1 	0
z 	2016/10 	2 	1
;
run;

data int (keep=product);
set have;
retain flag;
by product;
if first.product then flag = 1;
if diff < 0 then flag = 0;
if last.product and flag then output;
run;

data want;
merge
  have
  int (in=increasing)
;
by product;
if increasing;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 246 views
  • 2 likes
  • 6 in conversation