BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ARHarding
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
WesleyD
SAS Employee

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

 

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
ARHarding
Fluorite | Level 6

Thank you for the feedback! The next time I post I will do so with a data step. 🙂

WesleyD
SAS Employee

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

 

ARHarding
Fluorite | Level 6

YES! This is exactly the type of thing I was looking for! Thank you so much!

sas-innovate-2024.png

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.

 

Register now!

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
  • 4 replies
  • 456 views
  • 3 likes
  • 3 in conversation