Hi Team,
I got one request recently . It looks like this
id | sys_amount | actual_amount |
100 | 50 | 100 |
100 | 50 | 100 |
200 | 20 | 40 |
200 | 20 | 40 |
for example if the id 100 and for that we have 2 sys_amount and actual amouunt as 100 which is 100 (50+50) i need to create one extra column and need to highlight as "B"
The reqults should be like below
id | sys_amount | actual_amount | format |
100 | 50 | 100 | B |
100 | 50 | 100 | B |
200 | 20 | 40 | B |
200 | 20 | 40 | B |
150 | 100 | 100 | N |
800 | 25 | 56 | R |
for example if we have same id and and if we summarize the sys_amount if we are getting the amount in the actual amount the the format should be B and if the sys_amount and actual_amount is same format should be N like wise ...
Please help me on this
In my opinion, it would be a serious error to rely on the data always containing what you expect. I will define a couple of additional categories:
R = just 1 record for the ID, and the SYS_AMOUNT does not equal ACTUAL_AMOUNT (as in your sample data)
X = multiple records for the ID, and the SYS_AMOUNT does not equal the ACTUAL_AMOUNTs
Maybe these values will never be needed. But they should be accounted for in the programming logic.
Also note, it is possible that multiple values can be assigned for the same ID, if the amounts change within that ID.
Here's a program you can work with. It assumes your data set is sorted by ID, so you may need to sort it first.
data want;
calculated_total = 0;
do until (last.id);
set have;
by id;
calculated_total + sys_amount;
end;
do until (last.id);
set have;
by id;
if calculated_total = actual_amount then do;
if first.id=0 or last.id=0 then format='B';
else format='N';
end;
else do;
if first.id=0 or last.id=0 then format='X';
else format='R';
end;
output;
end;
run;
You can always change the assignments if you want a different set of values.
Good luck.
So are these definitions correct?
B = more than 1 record for the ID, and total of all SYS_AMOUNT is equal to ACTUAL_AMOUNT
N = just 1 record for the ID, and SYS_AMOUNT equals ACTUAL_AMOUNT
R = just 1 record for the ID, and SYS_AMOUNT does not equal ACTUAL_AMOUNT
Assuming these definitions are correct, here are a few more cases that need some definition:
?? = more than 1 record for the ID, and the ACTUAL_AMOUNT values change (some equal to the total SYS_AMOUNT, some not equal)
?? = just 1 record for the ID, and the SYS_AMOUNT equals ACTUAL_AMOUNT
It's not hard to program. It's more difficult to figure out the rules.
Hi the below definition you provided is correct
B = more than 1 record for the ID, and total of all SYS_AMOUNT is equal to ACTUAL_AMOUNT
N = just 1 record for the ID, and SYS_AMOUNT equals ACTUAL_AMOUNT
R = just 1 record for the ID, and SYS_AMOUNT does not equal ACTUAL_AMOUNT
If there are multiple ids then sys_amount and Actual_amount will be equal always .
Only need to get the above three rules.
Thanks
In my opinion, it would be a serious error to rely on the data always containing what you expect. I will define a couple of additional categories:
R = just 1 record for the ID, and the SYS_AMOUNT does not equal ACTUAL_AMOUNT (as in your sample data)
X = multiple records for the ID, and the SYS_AMOUNT does not equal the ACTUAL_AMOUNTs
Maybe these values will never be needed. But they should be accounted for in the programming logic.
Also note, it is possible that multiple values can be assigned for the same ID, if the amounts change within that ID.
Here's a program you can work with. It assumes your data set is sorted by ID, so you may need to sort it first.
data want;
calculated_total = 0;
do until (last.id);
set have;
by id;
calculated_total + sys_amount;
end;
do until (last.id);
set have;
by id;
if calculated_total = actual_amount then do;
if first.id=0 or last.id=0 then format='B';
else format='N';
end;
else do;
if first.id=0 or last.id=0 then format='X';
else format='R';
end;
output;
end;
run;
You can always change the assignments if you want a different set of values.
Good luck.
data have;
input id sys_amount actual_amount;
cards;
100 50 100
100 50 100
200 20 40
200 20 40
150 100 100
800 25 56
;
run;
proc sql;
create table want(drop=sum n) as
select *,sum(sys_amount) as sum,count(*) as n,
case when calculated n=2 and calculated sum=actual_amount then 'B'
when calculated n=1 and calculated sum=actual_amount then 'N'
when calculated n=1 and calculated sum ne actual_amount then 'R'
end as format
from have
group by id;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.