topic Re: How to match variables then compute in SAS Programming

How to match variables then compute

LaiQ — Mon, 25 Apr 2016 10:08:01 GMT

Hi all,

I have been trying to find answers to the subject title but still unable to find any. Hope that you guys can help me with this:

My datasets have date, ID and amount, for eg.

Date ID Amount

1 Jan AA 1000

1 Feb AA 500

1 Mar AA 100

1 Jan BB 1000

1 Jan CC 1000

1 Feb CC 500

What I want to achieve is to find the difference for each month, based on the ID. However, I do not know how to match the same ID then do the computation, meaning if first ID = second ID then difference = second amount - first amount.

The output maybe something like:

Date ID Amount Difference from previous month

1 Jan AA 1000

1 Feb AA 500 500

1 Mar AA 100 400

1 Jan BB 1000

1 Jan CC 1000

1 Feb CC 500 500

Really sorry for the poor formatting. And, I do not know how to use Macro and SQL. Hence, if it is possible to do without them, will really appreciate it. However, if either Macro or SQL is required, I will read up on the codes required.

Hope to get some advice/solution from you guys. Thank you.

Re: How to match variables then compute

gamotte — Mon, 25 Apr 2016 10:21:40 GMT

Hi,

Make sure that your dataset is sorted by ID (with a prior proc sort for instance).

Then you can use the "by id;" instruction in your data step.

This creates an automatic variable first.ID wich is 1 for the first observation of

each ID, zero otherwise.

The instruction "retain X Y;" allows You to keep the values of variables X and Y

from one observation to the other. So you can use retain to keep track of

the initial amount and the intermediary values of the calculated difference.

Re: How to match variables then compute

Reeza — Mon, 25 Apr 2016 10:29:32 GMT

You need to use BY group processing.

Assuming your data is sorted by ID.

Read up on BY groups, they're very powerful.

Data want;
Set have;

BY ID;

Diff = dif(amount);

If first.ID then diff = .;

Run;

Re: How to match variables then compute

Astounding — Mon, 25 Apr 2016 12:47:54 GMT

Reeza has the right idea. However, based on your example, you would need a slight change to the formula:

diff = - dif(amount);

Re: How to match variables then compute

ballardw — Mon, 25 Apr 2016 17:07:49 GMT

It may also help to ensure that your date is a SAS date valued variable. That will help sorting and lots of other things if they are not currently date valued.

Re: How to match variables then compute

LaiQ — Tue, 26 Apr 2016 02:41:43 GMT

hey, thank you!

Re: How to match variables then compute

LaiQ — Tue, 26 Apr 2016 02:42:23 GMT

got it! thank you!

Re: How to match variables then compute

LaiQ — Tue, 26 Apr 2016 02:42:48 GMT

okay! will try it out and see the results and adjust accordingly. thank you!

Re: How to match variables then compute

LaiQ — Tue, 26 Apr 2016 02:43:03 GMT

noted, will do! thank you!