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 |
It's not clear exactly what you want as output. Based on your input what's your expected output.
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.