Tag Archives: sql

Reflexive Joins in SAS with Proc SQL

Sometimes, you may want to use a table twice in the same query. When you join a table to itself*, it is called a reflexive join. In the example below, a common task when calculating admissions-related healthcare quality measures is to exclude transfers from admissions rates. When a patient is transferred from one facility to another within 24 hours of being discharged from the first facility, we do not want to count this as a separate admission. If we did, this would inflate our rates.

The parameterized macro below uses a reflexive join to identify transfers and delete them from the original file (designated by the parameter filename). The table is referenced twice in the FROM clause, first with alias A and then with alias B. We use member identifying information (fields rhmo and bkey) to join the table to itself. We also use the intnx function to calculate when an admission for a single member occurs within 24 hours of the discharge date. Finally, in the WHERE clause we specify that the readmission must have occurred at a different facility (src_sys_prov_id). The unique identifier for the admission (conf_id) is used in the DELETE operation to exclude these transfers from the original file.

%macro transfer(filename=)/STORE SOURCE;
CREATE TABLE &filename._TRANS AS
  SELECT B.conf_id, B.admit_dt, A.disch_dt
     FROM PQI.&filename A
       JOIN PQI.&filename B ON A.rhmo=B.rhmo AND A.bkey=B.bkey
           AND A.disch_dt BETWEEN B.admit_dt AND intnx(‘DAY’,B.admit_dt,-1)
               WHERE A.conf_id <> B.conf_id AND A.src_sys_prov_id <> B.src_sys_prov_id
;
DELETE
  FROM PQI.&filename
    WHERE conf_ID IN
      (SELECT conf_id FROM &filename._TRANS)
;
%mend transfer;

*Fun grammar fact! “Itself” is a reflexive pronoun, which we use in English when the subject of a verb is also the object. Such a verb is a reflexive verb. In Spanish, these verbs take on an entirely different form than other verbs, while in English, a reflexive verb can usually also be used in a non-reflexive way (example: “I dressed myself” vs. “I dressed my cat”).

Granting SELECT Access to Your Tables

If you have created a table in your own personal schema in an Oracle database, others do not have permissions to access that object. You may at some point wish to allow SELECT access to another user. To do this in SAS, you can use PROC DBLOAD as follows:

proc dbload dbms=odbc;
user=’yourusername‘;
password=’password‘;
dsn=database; /* this is the name of your ODBC Connection to the database. */
sql grant select on yourschema.yourtablename to anotherusername;
run;

Or you can use explicit pass-through syntax to grant access:

proc sql;
connect to oracle (user=yourusername password=password);
execute (grant select on yourusername.yourtablename
to anotherusername) by oracle;
disconnect from oracle;
quit;

Finally, you can do this directly in the Oracle database itself just by executing the following statement which is passed through in the syntax above:

grant select on yourusername.yourtablename to anotherusername;