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 |
Could you please attach/show some sample have and want tables?
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;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.