topic Re: group by in Dataset in SAS Programming

group by in Dataset

SASAna — Fri, 08 Sep 2017 17:59:14 GMT

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

Re: group by in Dataset

novinosrin — Fri, 08 Sep 2017 17:58:13 GMT

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

Re: group by in Dataset

SASAna — Fri, 08 Sep 2017 18:02:03 GMT

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

Re: group by in Dataset

SASAna — Fri, 08 Sep 2017 18:20:24 GMT

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;

Re: group by in Dataset

Reeza — Fri, 08 Sep 2017 19:25:05 GMT

You have no variable called: net_amt_pay

Your code and data do not align.

Re: group by in Dataset

novinosrin — Fri, 08 Sep 2017 19:32:01 GMT

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

Re: group by in Dataset

SASAna — Fri, 08 Sep 2017 19:42:45 GMT

Perfectly coded. Thanks for the help!

Thanks,
Ana

Re: group by in Dataset

novinosrin — Fri, 08 Sep 2017 19:47:48 GMT

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