BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LaiQ
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
gamotte
Rhodochrosite | Level 12

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
Calcite | Level 5
hey, thank you!
Reeza
Super User

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
PROC Star

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

 

diff = - dif(amount);

 

LaiQ
Calcite | Level 5
okay! will try it out and see the results and adjust accordingly. thank you!
LaiQ
Calcite | Level 5
got it! thank you!
ballardw
Super User

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
Calcite | Level 5
noted, will do! thank you!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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