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;
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)
Yes. Both are possible. Provide usable data in the form of a data step to recieve a usable code answer.
@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;
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;
@Kurt_Bremser Corrected the data now in initial post.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.