Help using Base SAS procedures

Sum over clients using Proc SQL

Reply
Frequent Contributor
Posts: 107

Sum over clients using Proc SQL

[ Edited ]

 

 

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   
clientyearmean_cost_2012Overall_Cost
120122557
120123257
22012232232
22013883883
32012232232
32012.232
32013323323

 

Want

clientyearmean_cost_2012Overall_Cost
120122557
120123257
22012232232
22013883883
32012232.
32012..
32013323323
Super User
Posts: 5,888

Re: Sum over clients using Proc SQL

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

Data never sleeps
Frequent Contributor
Posts: 107

Re: Sum over clients using Proc SQL

Sure, see message edited above. Thx
Super User
Posts: 10,787

Re: Sum over clients using Proc SQL


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;

Ask a Question
Discussion stats
  • 3 replies
  • 243 views
  • 0 likes
  • 3 in conversation