SQL to SAS - Having Trouble

[emmytheduck]

Okay, I do not know SQL, and I am fairly new to SAS. I have been tasked with converting this SQL code into SAS. I am only going to include the top part that is REALLY stumping me.

REFERRALS

DECLARE @TEMP_PROC1 TABLE

(REFERRAL_ID INT, PROC_CD VARCHAR(25), PROC_NAME VARCHAR(250),

PROC_CD_2 VARCHAR(25), PROC_NAME_2 VARCHAR(250), PROC_CD_3 VARCHAR(25), PROC_NAME_3 VARCHAR(250),

PROC_CD_4 VARCHAR(25), PROC_NAME_4 VARCHAR(250), PROC_CD_5 VARCHAR(25), PROC_NAME_5 VARCHAR(250),

PROC_CD_6 VARCHAR(25), PROC_NAME_6 VARCHAR(250), PROC_CD_7 VARCHAR(25), PROC_NAME_7 VARCHAR(250))

INSERT INTO @TEMP_PROC1

SELECT PX.REFERRAL_ID,

MAX((CASE WHEN PX.LINE = 1 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD,

MAX((CASE WHEN PX.LINE = 1 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME,

MAX((CASE WHEN PX.LINE = 2 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_2,

MAX((CASE WHEN PX.LINE = 2 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_2,

MAX((CASE WHEN PX.LINE = 3 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_3,

MAX((CASE WHEN PX.LINE = 3 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_3,

MAX((CASE WHEN PX.LINE = 4 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_4,

MAX((CASE WHEN PX.LINE = 4 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_4,

MAX((CASE WHEN PX.LINE = 5 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_5,

MAX((CASE WHEN PX.LINE = 5 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_5,

MAX((CASE WHEN PX.LINE = 6 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_6,

MAX((CASE WHEN PX.LINE = 6 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_6,

MAX((CASE WHEN PX.LINE = 7 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_7,

MAX((CASE WHEN PX.LINE = 7 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_7

FROM DATABASENAME.dbo.REFERRAL R

LEFT JOIN REFERRAL_PX AS PX ON R.REFERRAL_ID = PX.REFERRAL_ID

LEFT JOIN DATA_EAP AS EAP ON PX.PX_ID = EAP.PROC_ID

WHERE R.START_DATE >= '06/01/2015'

AND START_DATE <= '06/30/2015'

GROUP BY PX.REFERRAL_ID

Okay. So the PROC_CODE and PROC_NAME are each one field. The LINE field shows a value anywhere from 1 to 7. Basically, they have taken the LINE and said "okay, if LINE is 1, then show the PROC_CODE and PROC_NAME for the first occurrence. Then, do this for LINE 2 and make a new field called PROC_CODE_2 and PROC_NAME_2 and so on...

Any guidance is appreciated, and I am willing to give more info if needed. But that is the part that I can't seem to convert.

[Reeza]

Looks like a transpose to me. It may be worth using a proc transpose instead.

Take a look at the input and output data.

[Sonywell]

Try looking at it this way:

PROC SQL;

CREATE TABLE TEMP_PROC1 AS

SELECT PX.REFERRAL_ID,

               CASE WHEN PX.LINE = 1 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD,

               CASE WHEN PX.LINE = 1 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME,

               CASE WHEN PX.LINE = 2 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_2,

               CASE WHEN PX.LINE = 2 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_2,

               CASE WHEN PX.LINE = 3 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_3,

               CASE WHEN PX.LINE = 3 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_3,

               CASE WHEN PX.LINE = 4 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_4,

               CASE WHEN PX.LINE = 4 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_4,

               CASE WHEN PX.LINE = 5 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_5,

               CASE WHEN PX.LINE = 5 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_5,

               CASE WHEN PX.LINE = 6 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_6,

               CASE WHEN PX.LINE = 6 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_6,

               CASE WHEN PX.LINE = 7 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_7,

               CASE WHEN PX.LINE = 7 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_7

FROM DATABASENAME.dbo.REFERRAL R

LEFT JOIN REFERRAL_PX AS PX ON R.REFERRAL_ID = PX.REFERRAL_ID

LEFT JOIN DATA_EAP AS EAP ON PX.PX_ID = EAP.PROC_ID

WHERE R.START_DATE >= '06/01/2015'

AND START_DATE <= '06/30/2015';

Then followed by a group By

PROC SQL;

CREATE TABLE TEMP_PROC2 AS

SELECT REFERRAL_ID,

               MAX(PROC_CD) AS PROC_CD,

               MAX(PROC_NAME) as PROC_NAME,

               MAX(PROC_CD_2) AS PROC_CD_2, 

               MAX(PROC_NAME_2) as PROC_NAME_2,

               MAX(PROC_CD_3) AS PROC_CD_3,

               MAX(PROC_NAME_3) as PROC_NAME_3,

               MAX(PROC_CD_4) AS PROC_CD_4,

               MAX(PROC_NAME_4) as PROC_NAME_4,

               MAX(PROC_CD_5) AS PROC_CD_5,

               MAX(PROC_NAME_5) as PROC_NAME_5,

               MAX(PROC_CD_6) AS PROC_CD_6,

               MAX(PROC_NAME_6) as PROC_NAME_6,

               MAX(PROC_CD_7) AS PROC_CD_7,

               MAX(PROC_NAME_7) as PROC_NAME_7,

FROM TEMP_PROC1;

The original author is using the aggregate (MAX) with a group by to collapse seven rows of data into one while retaining the proc_cd and name values.

TEMP_PROC1 looks like:

REFERRAL_ID     PROC_CD     PROC_NAME     PROC_CD_2     PROC_NAME     PROC_CD_3     PROC_NAME ....

012345                V935              Surgery

012345                                                                  N0032               Anesthesia

012345                                                                                                                       V4333               Appendectomy

Which when aggregated (MAX) by REFERRAL_ID in TEMP_PROC2, becomes this:

REFERRAL_ID     PROC_CD     PROC_NAME     PROC_CD_2     PROC_NAME     PROC_CD_3     PROC_NAME ....

012345                V935              Surgery               N0032               Anesthesia          V4333               Appendectomy

[Patrick]

I agree with @Reeza that this looks like a Proc Transpose; but if you want to go for a "least code change" approach then something as below should work as well. What you will need to define in below code are SAS Libnames to the database tables so that you reference them correctly within SAS SQL. Eg. for "DATABASENAME.dbo.REFERRAL" you need to define a SAS Libname and then use the libref so in the SQL it needs to be something like "mylib.REFERRAL".

Are you already able to access the tables as such via SAS? If so then the librefs exist already (eg. as SAS metadata definitions) and you could use these.

proc sql;

  create table TEMP_PROC as

  SELECT PX.REFERRAL_ID length=8,

    MAX((CASE WHEN PX.LINE = 1 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD length=25,

    MAX((CASE WHEN PX.LINE = 1 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME length=250,

    MAX((CASE WHEN PX.LINE = 2 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_2length=25,

    MAX((CASE WHEN PX.LINE = 2 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_2length=250,

    MAX((CASE WHEN PX.LINE = 3 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_3length=25,

    MAX((CASE WHEN PX.LINE = 3 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_3length=250,

    MAX((CASE WHEN PX.LINE = 4 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_4length=25,

    MAX((CASE WHEN PX.LINE = 4 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_4length=250,

    MAX((CASE WHEN PX.LINE = 5 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_5length=25,

    MAX((CASE WHEN PX.LINE = 5 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_5length=250,

    MAX((CASE WHEN PX.LINE = 6 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_6length=25,

    MAX((CASE WHEN PX.LINE = 6 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_6length=250,

    MAX((CASE WHEN PX.LINE = 7 THEN EAP.PROC_CODE ELSE '' END)) AS PROC_CD_7length=25,

    MAX((CASE WHEN PX.LINE = 7 THEN EAP.PROC_NAME ELSE '' END)) AS PROC_NAME_7 length=250

  FROM DATABASENAME.dbo.REFERRAL R

  LEFT JOIN REFERRAL_PX AS PX ON R.REFERRAL_ID = PX.REFERRAL_ID

  LEFT JOIN DATA_EAP AS EAP ON PX.PX_ID = EAP.PROC_ID

  WHERE R.START_DATE >= '01Jun2015'd 

        AND START_DATE <= '30Jun2015'd

  GROUP BY PX.REFERRAL_ID

  ;

quit;

[emmytheduck]

Thank you everyone for the input. However, I ended up doing things a bit differently...I was able to get the entire thing into one PROC SQL. It looks something like this...

proc sql;

    create table Referrals as

    select a.REFERRAL_ID, a.ENTRY_DATE, a.START_DATE, a.SERV_DATE, s.NAME AS RFL_STATUS label='RFL_STATUS', r.NAME AS RFL_TYPE label='RFL_TYPE',

    q1.PROV_NAME as PCP_NAME label='PCP_NAME', q.PROV_NAME as REFERRING_PROV label='REFERRING_PROV',

    q2.PROV_NAME as REFERRED_TO_PROV label='REFERRED_TO_PROV', p.VENDOR_NAME AS REFERRED_TO_VENDOR label='REFERRED_TO_VENDOR', a.PAT_ID, t.PAT_NAME,

    i.PROC_CODE as PROC_CODE_1 label='PROC_CODE_1', i.PROC_NAME as PROC_NAME_1 label='PROC_NAME_1',

     j.PROC_CODE as PROC_CODE_2 label='PROC_CODE_2', j.PROC_NAME as PROC_NAME_2 label='PROC_NAME_2',

     k.PROC_CODE as PROC_CODE_3 label='PROC_CODE_3', k.PROC_NAME as PROC_NAME_3 label='PROC_NAME_3',

     l.PROC_CODE as PROC_CODE_4 label='PROC_CODE_4', l.PROC_NAME as PROC_NAME_4 label='PROC_NAME_4',

     m.PROC_CODE as PROC_CODE_5 label='PROC_CODE_5', m.PROC_NAME as PROC_NAME_5 label='PROC_NAME_5',

     n.PROC_CODE as PROC_CODE_6 label='PROC_CODE_6', n.PROC_NAME as PROC_NAME_6 label='PROC_NAME_6',

     o.PROC_CODE as PROC_CODE_7 label='PROC_CODE_7', o.PROC_NAME as PROC_NAME_7 label='PROC_NAME_7'

from (select * from database.referral where datepart(START_DATE) >= '01-JUL-2015'd and datepart(START_DATE) <= '30-JUL-2015'd and RFL_STATUS_C ne 4) a

    left join (select * from database.referral_px where line = 1) b on a.REFERRAL_ID = b.REFERRAL_ID

    left join (select * from database.referral_px where line = 2) c on a.REFERRAL_ID = c.REFERRAL_ID

    left join (select * from database.referral_px where line = 3) d on a.REFERRAL_ID = d.REFERRAL_ID

    left join (select * from database.referral_px where line = 4) e on a.REFERRAL_ID = e.REFERRAL_ID

    left join (select * from database.referral_px where line = 5) f on a.REFERRAL_ID = f.REFERRAL_ID

    left join (select * from database.referral_px where line = 6) g on a.REFERRAL_ID = g.REFERRAL_ID

    left join (select * from database.referral_px where line = 7) h on a.REFERRAL_ID = h.REFERRAL_ID

    left join database.cl_eap i on b.PX_ID = i.PROC_ID

    left join database.cl_eap j on c.PX_ID = j.PROC_ID

    left join database.cl_eap k on d.PX_ID = k.PROC_ID

    left join database.cl_eap l on e.PX_ID = l.PROC_ID

    left join database.cl_eap m on f.PX_ID = m.PROC_ID

    left join database.cl_eap n on g.PX_ID = n.PROC_ID

    left join database.cl_eap o on h.PX_ID = o.PROC_ID

    left join database.cl_VENDOR p on a.VENDOR_ID = p.VENDOR_ID

    left join database.cl_SER q on a.REFERRING_PROV_ID = q.PROV_ID

    left join database.cl_SER q1 on a.PCP_PROV_ID = q1.PROV_ID

    left join database.cl_SER q2 on a.REFERRAL_PROV_ID = q2.PROV_ID

    left join database.ZC_RFL_TYPE r on a.RFL_TYPE_C = r.RFL_TYPE_C

    left join database.ZC_RFL_STATUS s on a.RFL_STATUS_C = s.RFL_STATUS_C

    left join database.PATIENT t on a.PAT_ID = t.PAT_ID;

quit;

There were obviously QUITE a few tables that I needed to add in to get all of my fields. The code works perfectly and produces exactly what I need. Though, I'm still going to try some of the above methods!