BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ebills
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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;

View solution in original post

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

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
Fluorite | Level 6

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
Barite | Level 11

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2628 views
  • 0 likes
  • 2 in conversation