To Subtract the amount from the same column

[AshokD]

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!..

[LinusH]

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.

[RW9]

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.

[Reeza]

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