topic Data manipulation help needed in SAS Programming

Data manipulation help needed

ARHarding — Thu, 27 Jul 2017 10:10:29 GMT

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.

Re: Data manipulation help needed

RW9 — Thu, 27 Jul 2017 10:51:10 GMT

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;

Re: Data manipulation help needed

WesleyD — Thu, 27 Jul 2017 11:03:57 GMT

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

Re: Data manipulation help needed

ARHarding — Thu, 27 Jul 2017 11:50:04 GMT

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

Re: Data manipulation help needed

ARHarding — Thu, 27 Jul 2017 13:02:02 GMT

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