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
@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;
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
You have no variable called: net_amt_pay
Your code and data do not align.
@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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.