topic Re: Sum over clients using Proc SQL in SAS Procedures

Sum over clients using Proc SQL

GKati — Mon, 18 Sep 2017 12:07:12 GMT

Hello,

I'm trying to add up mean_costs of claims over clients. For some claims and certain years I don't have the mean-cost, which appears as missing in my dataset. In these cases I want overall-costs for that client to be also missing. How do I put this in my code below. Sorry I am not very good in SQL. Thanks

proc sql;

create table work.final_claims as

select *

, sum(mean_cost_2012) as overall_cost1016

from work.final_claims

group by client, year

order by client year;

quit;

Have
client	year	mean_cost_2012	Overall_Cost
1	2012	25	57
1	2012	32	57
2	2012	232	232
2	2013	883	883
3	2012	232	232
3	2012	.	232
3	2013	323	323

Want

client	year	mean_cost_2012	Overall_Cost
1	2012	25	57
1	2012	32	57
2	2012	232	232
2	2013	883	883
3	2012	232	.
3	2012	.	.
3	2013	323	323

Re: Sum over clients using Proc SQL

LinusH — Mon, 18 Sep 2017 08:11:29 GMT

Could you please attach/show some sample have and want tables?

Re: Sum over clients using Proc SQL

GKati — Mon, 18 Sep 2017 12:08:08 GMT

Sure, see message edited above. Thx

Re: Sum over clients using Proc SQL

Ksharp — Mon, 18 Sep 2017 12:56:27 GMT


data Have;	 	 	 
input client	year	mean_cost_2012	;
cards;
1	2012	25	57
1	2012	32	57
2	2012	232	232
2	2013	883	883
3	2012	232	232
3	2012	.	232
3	2013	323	323
;
run;

proc sql;
  create table work.final_claims as
  select *
  ,case when exists(select * from have where mean_cost_2012 is missing and client=a.client and year=a.year)
then .  else sum(mean_cost_2012) end as overall_cost1016
  from work.have as a
  group by client, year
  order by client, year;
quit;