turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Data manipulation help needed

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-27-2017 06:10 AM

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

07-27-2017
07:50 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ARHarding

07-27-2017 06:58 AM - edited 07-27-2017 07:03 AM

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
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ARHarding

07-27-2017 06:51 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-27-2017 09:02 AM

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

Solution

07-27-2017
07:50 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ARHarding

07-27-2017 06:58 AM - edited 07-27-2017 07:03 AM

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
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WesleyD

07-27-2017 07:50 AM

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