topic Re: To Subtract the amount from the same column in SAS Programming

To Subtract the amount from the same column

AshokD — Wed, 09 Mar 2016 14:39:05 GMT

Hi All,

My file contains the below data.

Employee Id Sal ID Amount
27000002 1900 0.00
27000002 2100 78.00
27000002 3600 100.00
27000002 7500 110.00

I need to calculate the amount for the Sal ID 3600 = 3600(100.00) - 2100(78.00) and rest all the Sal ID amounts needs to be reported as it in the file.

I'm not sure , how to subtract the value from the same column.

Please share your thoughts.

Thanks in advance for your help!..

Re: To Subtract the amount from the same column

LinusH — Wed, 09 Mar 2016 15:05:04 GMT

Not sure about the business rule here. Could it be that certain Sal ID should always be calculated as minus?

If so, have a lookup table ("dimension") with each Sal ID and with a mult column (1 or -1).

Join back with your transaction data multiply with your Amount.

Then it's not clear how you wish to classify the row with the result, is id Sal ID 3600, or perhaps it should be assigned a new id, since it has been transformed...

Again, this can be handled in a lookup/dimension table.

Re: To Subtract the amount from the same column

RW9 — Wed, 09 Mar 2016 15:17:47 GMT

Could you clarify a bit. Why is 3600 chosen as the start, is it becaue it is 100%? Does everything from that point on need to taken of the sal id? If so then a reverse sort would be best:

proc sort data=have;

by employee_id descending sa_id;

run;

data want;

set have;

retain result;

by employee_id;

if first.employee_id then result=.;

if amount=100 then result=sal_id;

else if result ne . then result=result - sal_id;

run;

Also, posting test data in a datastep, i.e. on we can copy and paste directly is more likely to get you tested code.

Re: To Subtract the amount from the same column

Reeza — Wed, 09 Mar 2016 15:51:17 GMT

Also, look at LAG functions, but note that they don't work in IF statements.