DATA Step, Macro, Functions and more

To Subtract the amount from the same column

Reply
New Contributor
Posts: 3

To Subtract the amount from the same column

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

Super User
Posts: 5,426

Re: To Subtract the amount from the same column

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.

Data never sleeps
Super User
Super User
Posts: 7,942

Re: To Subtract the amount from the same column

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.

 

Super User
Posts: 19,784

Re: To Subtract the amount from the same column

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

Ask a Question
Discussion stats
  • 3 replies
  • 255 views
  • 0 likes
  • 4 in conversation