Need to format the raws with different values

[ambadi007]

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

[Astounding]

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.

[ambadi007]

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

[Astounding · Accepted solution]

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.

[Ksharp]

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;