Left Join two nested queries issue - Proc SQL

[ebills]

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!

[Steelers_In_DC · Accepted solution]

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;

[ebills]

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!

[Steelers_In_DC]

My code is usually simple, I come here to get more elegant solutions.  Glad I could help.