Hey all, Thanks for taking the time to read this:I'm trying to create a report that shows PatID, Episode_number, Guarantor, GuarantorCost (as there can be many) and then a column with Total Services Cost. I would then like a weighted column that calculates what each guarantor paid per episode, per patient.
Example Want:
Patient Episode Guarantor GCost TotalCost Insurance_Coverage_Weight
2222 4 89 $75 $100 (I need help on this formular/calculation too)
2222 5 13 $25 $100
142 1 201 $60 $300
142 2 201 $100 $300
142 3 201 $140 $300
The problem I'm having is that my output, after left joining, is now giving me something like this:
Patient Episode Guarantor TotalCost
2222 4 89 $100
2222 5 13 $100
142 1 201 $300
142 2 201 $300
142 3 201 $300
Here is my code:
proc sql inobs=100000;
select
f.patid,
f.episode_number,
f.totalcost,
n.primary_guarantor
from
(
select
patid,
episode_number,
primary_guarantor,
sum(cost_of_service) format=dollar12.2
as TotalCost
from guarantor
(keep=
patid
episode_number
primary_guarantor
cost_of_service)
where cost_of_service>0
group by patid, episode_number, primary_guarantor
) as n
left join
(select
patid,
episode_number,
sum(cost_of_service)
format dollar10.2
as TotalCost
from final
(keep=patid episode_number cost_of_service)
where cost_of_service is not missing
group by patid, episode_number
) as f
on f.patid=n.patid
and f.episode_number=n.episode_number
order by f.patid, f.episode_number;
quit;
My boss (as he's just dropping clues since I'm fairly new to SAS/SQL) did mention that I need to create the GuarantorCost in my Numerator...but I just don't get it, as it's just the same calculation on the same variable, right? What am I missing. I tried to change the numerator query to GuarantorCost and then the log just says that 'totalcost' could not be found in the table/view.
Any help would be appreciated! Thanks!
Take a look at this, might just be a start but I think this is what you are looking for:
data have;
infile cards;
informat gcost dollar4.;
format gcost dollar4.;
input Patient $ Episode Guarantor GCost;
cards;
2222 4 89 $75
2222 5 13 $25
142 1 201 $60
142 2 201 $100
142 3 201 $140
;
run;
proc sql;
create table start as
select *,sum(gcost) as tcost format=dollar4.
from have
group by patient;
data want;
set start;
format weight percent.;
weight=gcost/tcost;
run;
Take a look at this, might just be a start but I think this is what you are looking for:
data have;
infile cards;
informat gcost dollar4.;
format gcost dollar4.;
input Patient $ Episode Guarantor GCost;
cards;
2222 4 89 $75
2222 5 13 $25
142 1 201 $60
142 2 201 $100
142 3 201 $140
;
run;
proc sql;
create table start as
select *,sum(gcost) as tcost format=dollar4.
from have
group by patient;
data want;
set start;
format weight percent.;
weight=gcost/tcost;
run;
Thanks Mark. My brain was going a million miles a minute trying to put things into perspective and your simplistic code succeeded in settling the scatter and giving me a good base to build on.
I appreciate it!
My code is usually simple, I come here to get more elegant solutions. Glad I could help.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.