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!..
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.
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.
Also, look at LAG functions, but note that they don't work in IF statements.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.