Sum and Group by

[David_Billa]

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;

[PeterClemmensen]

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

[David_Billa]

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

[David_Billa]

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

[Kurt_Bremser]

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;

---

[David_Billa]

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

[David_Billa]

@Kurt_Bremser Corrected the data now in initial post.

[Kurt_Bremser · Accepted solution]

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)