DATA Step, Macro, Functions and more

How to match variables then compute

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

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: 17,944

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;

View solution in original post


All Replies
Regular Contributor
Posts: 194

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: 17,944

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: 5,097

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: 10,548

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 301 views
  • 0 likes
  • 5 in conversation