DATA Step, Macro, Functions and more

group by in Dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

group by in Dataset

[ Edited ]

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


Accepted Solutions
Solution
‎09-08-2017 03:42 PM
PROC Star
Posts: 1,349

Re: group by in Dataset

@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


All Replies
PROC Star
Posts: 1,349

Re: group by in Dataset

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

Frequent Contributor
Posts: 123

Re: group by in Dataset

Posted in reply to novinosrin
Hi Novinsrin, i have edited the original message to add test data , along with expected custom value
Frequent Contributor
Posts: 123

Re: group by in Dataset

Posted in reply to novinosrin
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;

Super User
Posts: 22,873

Re: group by in Dataset

You have no variable called: net_amt_pay

 

Your code and data do not align.

Solution
‎09-08-2017 03:42 PM
PROC Star
Posts: 1,349

Re: group by in Dataset

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

Frequent Contributor
Posts: 123

Re: group by in Dataset

Posted in reply to novinosrin
Perfectly coded. Thanks for the help!

Thanks,
Ana
PROC Star
Posts: 1,349

Re: group by in Dataset

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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