Hello, this is my first post here in the SAS forums!
I have a data set which requires a manipulation that I am unable to figure out how to do.
It's set up like this:
fruit count day
apples 500 1
apples 300 3
apples 505 5
apples 487 7
oranges 36 1
oranges 39 5
oranges 42 7
pears 17 1
pears 16 2
pears 13 3
pears 21 5
pears 27 6
What I need to do is find the difference in number of fruit on each day from the first day. So for apples, I need to calculate the difference in # of apples between days 1 and 3, 1 and 5, 1 and 7. For oranges, days 1 and 5, 1 and 7, etc. The difference on each day will be stored in a new variable.
Can anyone help with this? Or provide suggestions? I do most of my work in R, and trying to do this in SAS is obviously not the same.
You could try something like:
data my_fruit(drop=_count);
set my_fruit;
by fruit;
retain _count;
if first.fruit then _count=count;
diff=abs(_count-count);
run;
This returns:
fruit count day diff
apples 500 1 0
apples 300 3 200
apples 505 5 5
apples 487 7 13
oranges 36 1 0
oranges 39 5 3
oranges 42 7 6
pears 17 1 0
pears 16 2 1
pears 13 3 4
pears 21 5 4
pears 27 6 10
First, when posting test data, please do so in the form of a datastep (such as in my code below).
There are a number of ways of approaching this (and you have not shown what the output should look like so its hard to choose one). Transpose and arrays for instance:
data have; infile datalines dlm=","; input fruit $ count day; datalines; apples,500,1 apples,300,3 apples,505,5 apples,487,7 oranges,36,1 oranges,39,5 oranges,42,7 ; run; proc transpose data=have out=want (drop=_name_) prefix=day; by fruit; var count; id day; run; data want (drop=i); set want; array vals{*} day:; do i=2 to dim(vals); vals{i}=vals{i}-vals{1}; end; run;
Or you could calculate using a retianed variable and then transpose afterwards:
data inter; set have; retain day1; if day=1 then day1=count; count=count-day1; run; proc transpose data=inter out=want (drop=_name_) prefix=day; by fruit; var count; id day; run;
Thank you for the feedback! The next time I post I will do so with a data step. 🙂
You could try something like:
data my_fruit(drop=_count);
set my_fruit;
by fruit;
retain _count;
if first.fruit then _count=count;
diff=abs(_count-count);
run;
This returns:
fruit count day diff
apples 500 1 0
apples 300 3 200
apples 505 5 5
apples 487 7 13
oranges 36 1 0
oranges 39 5 3
oranges 42 7 6
pears 17 1 0
pears 16 2 1
pears 13 3 4
pears 21 5 4
pears 27 6 10
YES! This is exactly the type of thing I was looking for! Thank you so much!
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.