Left Join two nested queries issue - Proc SQL

ebills — Fri, 17 Apr 2015 17:01:31 GMT

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!

Re: Left Join two nested queries issue - Proc SQL

Steelers_In_DC — Fri, 17 Apr 2015 17:15:03 GMT

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;

Re: Left Join two nested queries issue - Proc SQL

ebills — Fri, 17 Apr 2015 20:39:20 GMT

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!

Re: Left Join two nested queries issue - Proc SQL

Steelers_In_DC — Thu, 30 Apr 2015 12:59:32 GMT

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

topic Left Join two nested queries issue - Proc SQL in SAS Procedures

Left Join two nested queries issue - Proc SQL

Re: Left Join two nested queries issue - Proc SQL

Re: Left Join two nested queries issue - Proc SQL

Re: Left Join two nested queries issue - Proc SQL