New to SAS/SQL; Need help creating a program!

Reply
Occasional Contributor
Posts: 9

New to SAS/SQL; Need help creating a program!

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!

Grand Advisor
Posts: 17,320

Re: New to SAS/SQL; Need help creating a program!

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.

Respected Advisor
Posts: 4,606

Re: New to SAS/SQL; Need help creating a program!

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
Grand Advisor
Posts: 10,210

Re: New to SAS/SQL; Need help creating a program!

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.

Valued Guide
Posts: 3,206

Re: New to SAS/SQL; Need help creating a program!

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 --<-----
Ask a Question
Discussion stats
  • 4 replies
  • 247 views
  • 9 likes
  • 5 in conversation