BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have a dataset similar to the below in which i have a column which contains account number and transaction amount. And i have situation in which i have to minus trans_amt(i.e 1stobs-2ndobs) by account number.

Ex:
data a;
input account_no trans_amt;
datalines;
100 1000
100 500
100 2000
200 3000
200 2000
;

Output should like this :

account_no trans_amt amount
100 1000 500
100 500 -1500
100 2000 0
200 3000 1000
200 2000 0
2 REPLIES 2
Olivier
Pyrite | Level 9
If you make a copy of the dataset, deleting the first observation for each account, and then merging the copy back with the original dataset, you will have the current value and the next one on the same row of data, allowing you to substract.
For example :
[pre]
data a;
input account_no trans_amt;
datalines;
100 1000
100 500
100 2000
200 3000
200 2000
;
run;
data a2 ;
set a (rename=(trans_amt = next_amt)) ;
by account_no ;
if first.account_no then delete ;
run ;
data result (drop=next_amt) ;
merge a a2 ;
by account_no ;
amount = trans_amt-next_amt ;
run ;
proc print ;
run ;
[/pre]
Regards,
Olivier
deleted_user
Not applicable
You can also do it as follows:

Step 1:

Index the obsevations with a new variable say IDX. The o/p will look like
account_no trans_amt idx
100 1000 1
100 500 2
100 2000 3
200 3000 1
200 2000 2

Use first.account_no to initialize the idx.

Step 2:

Sort the data on idx desc. The o/p will be something like:
100 2000 3
100 500 2
100 1000 1
200 2000 2
200 3000 1

Step 3:
Now using RETAIN do the subtraction. o/p would be like:
account_no transaction_amt amount idx
100 2000 0 3
100 500 -1500 2
100 1000 500 1
200 2000 0 2
200 3000 1000 1

Step 4:
Sort on idx ascending and get rid of it. o/p should look the way you want it to.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 673 views
  • 0 likes
  • 2 in conversation