03-09-2016 09:39 AM
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!..
03-09-2016 10:05 AM
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.
03-09-2016 10:17 AM
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;
if first.employee_id then result=.;
if amount=100 then result=sal_id;
else if result ne . then result=result - sal_id;
Also, posting test data in a datastep, i.e. on we can copy and paste directly is more likely to get you tested code.