BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

 

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
5 REPLIES 5
Reeza
Super User

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

PeterClemmensen
Tourmaline | Level 20

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;

 

 

ballardw
Super User

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.

 

Astounding
PROC Star

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;

Kurt_Bremser
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 1333 views
  • 2 likes
  • 6 in conversation