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!
