Solved
Contributor
Posts: 31

# How to match variables then compute

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.

Accepted Solutions
Solution
‎04-25-2016 10:41 PM
Super User
Posts: 23,661

## Re: How to match variables then compute

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

All Replies
Super Contributor
Posts: 340

## Re: How to match variables then compute

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.

Contributor
Posts: 31

## Re: How to match variables then compute

hey, thank you!
Solution
‎04-25-2016 10:41 PM
Super User
Posts: 23,661

## Re: How to match variables then compute

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;``````
Super User
Posts: 6,751

## Re: How to match variables then compute

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

diff = - dif(amount);

Contributor
Posts: 31

## Re: How to match variables then compute

okay! will try it out and see the results and adjust accordingly. thank you!
Contributor
Posts: 31

## Re: How to match variables then compute

got it! thank you!
Super User
Posts: 13,498

## Re: How to match variables then compute

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.

Contributor
Posts: 31

## Re: How to match variables then compute

noted, will do! thank you!
🔒 This topic is solved and locked.