BookmarkSubscribeRSS Feed
ebills
Fluorite | Level 6

Hey all,

Please bare with me, as I am very new to SAS/SQL. I took the online tutorial classes for SAS and dug deep in text books and youtube videos. I thought I had a good grasp on the basics and then my boss let me know that he'd like my first task to be done using SQL.

So, I'm still trying to wrap my mind around it all; is this done in conjunction with one another, how am I going to do this most effectively, etc.

So, here is my task. There's a LOT of data (observations).

  • From a bunch of different tables (admissions, disharges, fiscal) I have to create a program that shows the current tenancy of patients in various hospitals.
    • as far as variables are concerned, I have admission dates, discharge dates, hospital codes (in lieu of the hospital name) and patient ID's.
    • many patients are listed numerous times for past visits.
  • I would like to left join the admissions/discharges so that it if a value is missing from a discharge, then obviously the patient is still there.

Any help would be greatly appreciated. I just don't even know where to begin and is this even possible by just doing SQL, or do I switch off between traditional SAS steps and SQL?

Thanks!

4 REPLIES 4
Reeza
Super User

First, figure out how to match which admission with a discharge, is there a visit ID for starters?

If so you can do a left join and your admissions without discharges will be clear.

Or you can check which visitID's are not in the discharge table. 

Yes, this can all be done in SQL given what you've stated so far.

PGStats
Opal | Level 21

To add to 's advice, look into the COALESCE function to replace missing discharge dates with today's date, as in


coalesce(DISCHARGE.date, today() + 1) - ADMISSION.date as tenancy,

PG

PG
ballardw
Super User

One approach:

Get the latest admin date, if it is a SAS date variable ( and really should be) then the MAX when grouped by patient id and hospital

Do the same for the discharge date

Join those two. If the discharge date is missing OR less than admin they either 1) are still in the hospital or 2) the records aren't quite up to date. (hint: where admin_date > discharge_date)

Of course this will all be as of the date/time the data was extracted.

It may be advantageous to combine multiple admit sets into one and the same for discharge keeping the minimum amount of data.

As an aside about your comment of hospital codes instead of names: Rejoice! Codes can be kept for the same building (or other concept of continuity) where as Hospitals may change names for marketing or other purpose such as merger and you lose continuity. I deal with the reverse of this in School names without a code. Every year I have to a bunch of sleuthing about are new names actually renamed old schools a are missing previously listed schools actually closed or renamed or typos in data entry.

jakarman
Barite | Level 11

What kind or version/dialect of SQL he is wanting. It he is thinking all SQL is equal that is a failure from start. For instance with SAS datasets you are having 2 value logic (true/false). Within a RDBMS there is 3 values logic (true-false-uknown) the last better known as NULL. 

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 686 views
  • 9 likes
  • 5 in conversation