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;
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.
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.