topic Sum and Group by in SAS Programming

Sum and Group by

David_Billa — Mon, 08 Jun 2020 06:14:50 GMT

I've the data as follows and I want to calculate the variable 'LOBS' and the calculation should be like sum(PREM) group by Segment and ID / DIV value by Segment.

It means sum of PREM by Segment and ID and divide by DIV of respective Segment value. Is there a way that we do this calculation in one step (data or proc sql)?

For DIV we have Input values based on SEGMENT.

Data step is below with the sample data.

data have;
input ID SEGMENT $ PREM DIV;
datalines;
1000 NL 0.124 0.327
1000 HS 0.187 0.567
1000 HS 0.098 0.567
1100 NL 0.123 0.327
1100 NL 0.175 0.327
1100 NL 0.123 0.327
1100 HS 0.342 0.567
1100 HS 0.126 0.567
;
run;

Re: Sum and Group by

PeterClemmensen — Sun, 07 Jun 2020 21:15:57 GMT

Yes. Both are possible. Provide usable data in the form of a data step to recieve a usable code answer.

Re: Sum and Group by

David_Billa — Mon, 08 Jun 2020 03:07:11 GMT

In the meantime can I know the logic to accomplish it?

Re: Sum and Group by

David_Billa — Mon, 08 Jun 2020 06:15:39 GMT

@PeterClemmensen - Data step is below with the sample data.

data have;

input ID SEGMENT $ PREM DIV;

datalines;

1000 NL 0.124 0.327

1000 HS 0.187 0.567

1000 HS 0.098 0.567

1100 NL 0.123 0.327

1100 NL 0.175 0.327

1100 NL 0.123 0.327

1100 HS 0.342 0.567

1100 HS 0.126 0.567

;

run;

Re: Sum and Group by

Kurt_Bremser — Mon, 08 Jun 2020 05:37:51 GMT

I guess your second dataline is incorrect, as it creates an ambiguous value of DIV for segment NL.

@David_Billa wrote:

Data step is below with the sample data.

data have;
input ID SEGMENT $ PREM DIV;
datalines;
1000 NL 0.124 0.327
1000 NL 0.125 0.567
1000 HS 0.187 0.567
1000 HS 0.098 0.567
1100 NL 0.123 0.327
1100 NL 0.175 0.327
1100 NL 0.123 0.327
1100 HS 0.342 0.567
1100 HS 0.126 0.567
;
run;

Re: Sum and Group by

David_Billa — Mon, 08 Jun 2020 06:06:11 GMT

That's fine. You can remove the incorrect entry. I'm looking for logic to
implement it.

Re: Sum and Group by

David_Billa — Mon, 08 Jun 2020 06:16:59 GMT

@Kurt_Bremser Corrected the data now in initial post.

Re: Sum and Group by

Kurt_Bremser — Mon, 08 Jun 2020 06:59:22 GMT

Then it would be

proc sql;
create table want as
  select
    *,
    sum(prem) / div
  from have
  group by id, segment
;
quit;

(untested, posted from my tablet)