group by in Dataset

[SASAna]

Hi SAS USers,

I needed some help in populating the custom field by the claim_id  with sum(net amt pay), currently with the below logic i am getting 0.00 in the field.

Example data and expected custom calculation - 

clm_id   status   paid_amt      custom

1             Q            100.00       0

1             A              0               300.00

1            Q              200.00     0

1            Q               0             0

proc sort data = Output1;

by clm_id;

run;

data Output2;

set Output1;

by clm_id;

if status = 'A' then Custom = put(sum(net_amt_pay), 8.2);

else Custom = '';

run;

Thanks,

Ana

[novinosrin]

What is the sum(net_amt_pay) SQL style column summation doing in datastep that does row wise processing? Show a sample of your input and output

[SASAna]

Hi Novinsrin, i have edited the original message to add test data , along with expected custom value

[SASAna]

how about this new changes -

data Output2;
set Output1;
by clm_id;
retain custom1;
if first.clm_id then do;
custom1 = 0;
end;
Custom1 = put(sum(net_amt_pay , custom1), 8.2);
if status ='A' then custom = custom1; else custom = '';
run;

[Reeza]

You have no variable called: net_amt_pay

Your code and data do not align.

[novinosrin · Accepted solution]

@SASAna  Do you want Datastep or Proc sql? If datastep , are you comfortable with DOW double?

data have;

input clm_id   status $  paid_amt  ;

datalines;

1             Q            100.00     

1             A              0             

1            Q              200.00  

1            Q               0         

;

data want;

do until(last.clm_id);

set have;

by clm_id;

custom+paid_amt;

if last.clm_id then _custom=custom;

end;

do until(last.clm_id);

set have;

by clm_id;

if not(status='A') then call missing(custom);

else if status='A' then custom=_custom;

output;

end;

drop _:;

run;

[SASAna]

Perfectly coded. Thanks for the help!

Thanks,
Ana

[novinosrin]

And, If you wanna fun playing with Proc sql:

proc sql;

create table want as

select a.*,case   when a.status='A' then b.custom   else .     end as custom

from have a, (select clm_id,sum(paid_amt) as custom from have group by clm_id having status='A') b

where a.clm_id=b.clm_id;

quit;

Regards,

Naveen Srinivasan