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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.