topic Re: Hi All, Subtracting Values (1stObs-2nd obs ) in SAS Programming

Hi All, Subtracting Values (1stObs-2nd obs )

deleted_user — Thu, 07 Aug 2008 04:35:06 GMT

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

Re: Hi All, Subtracting Values (1stObs-2nd obs )

Olivier — Thu, 07 Aug 2008 07:51:16 GMT

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

Re: Hi All, Subtracting Values (1stObs-2nd obs )

deleted_user — Thu, 07 Aug 2008 09:03:56 GMT

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.