BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

@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;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8
Hi Novinsrin, i have edited the original message to add test data , along with expected custom value
SASAna
Quartz | Level 8
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
Super User

You have no variable called: net_amt_pay

 

Your code and data do not align.

novinosrin
Tourmaline | Level 20

@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
Quartz | Level 8
Perfectly coded. Thanks for the help!

Thanks,
Ana
novinosrin
Tourmaline | Level 20

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 901 views
  • 2 likes
  • 3 in conversation