DATA Step, Macro, Functions and more

Need to format the raws with different values

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Need to format the raws with different values

Hi Team,

 

I got one request recently . It looks like this

 

idsys_amountactual_amount
10050100
10050100
2002040
2002040

 

 

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

 

idsys_amountactual_amountformat
10050100B
10050100B
2002040B
2002040B
150100100N
8002556R

 

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


Accepted Solutions
Solution
‎03-21-2016 04:59 AM
Super User
Posts: 5,516

Re: Need to format the raws with different values

Posted in reply to ambadi007

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.

View solution in original post


All Replies
Super User
Posts: 5,516

Re: Need to format the raws with different values

Posted in reply to ambadi007

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.

Contributor
Posts: 64

Re: Need to format the raws with different values

Posted in reply to Astounding

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

Solution
‎03-21-2016 04:59 AM
Super User
Posts: 5,516

Re: Need to format the raws with different values

Posted in reply to ambadi007

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.

Super User
Posts: 10,044

Re: Need to format the raws with different values

Posted in reply to ambadi007
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 273 views
  • 1 like
  • 3 in conversation