How to match variables then compute

[LaiQ]

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.

[gamotte]

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.

[LaiQ]

hey, thank you!

[Reeza · Accepted solution]

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;

[Astounding]

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

diff = - dif(amount);

[LaiQ]

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

[LaiQ]

got it! thank you!

[ballardw]

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.

[LaiQ]

noted, will do! thank you!