Sum over clients using Proc SQL

[GKati]

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

[LinusH]

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

[GKati]

Sure, see message edited above. Thx

[Ksharp]

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;