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