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!
... View more
