Data manipulation help needed

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Data manipulation help needed

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.


Accepted Solutions
Solution
3 weeks ago
SAS Employee
Posts: 5

Re: Data manipulation help needed

[ Edited ]

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


All Replies
Super User
Super User
Posts: 7,392

Re: Data manipulation help needed

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;
New Contributor
Posts: 3

Re: Data manipulation help needed

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

Solution
3 weeks ago
SAS Employee
Posts: 5

Re: Data manipulation help needed

[ Edited ]

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

 

New Contributor
Posts: 3

Re: Data manipulation help needed

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 98 views
  • 3 likes
  • 3 in conversation