Help using Base SAS procedures

PROC SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

PROC SQL

Hi Team,

I have problems connecting these two pieces of code into ONE TABLE

Could some one help me solve this problem??

Thanks

/*--HAC1*/

PROC SQL;
CREATE TABLE tempHAC AS
SELECT
'HAC1' AS Measure,

dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HaC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID and
       acc.DateDischargeKey = dat.DateKey and
    diag.DiagnosisKey = ddiag.DiagnosisKey and
diag.Line ne 1 and
diag.DX_POA_YNU IN ("N","U") and
acc.DateDischargeKey >= &BegDate
AND acc.DateDischargeKey <= &EndDate

AND ddiag.ICD9_Code IN ( 
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC1')/*--('998.4','998.7')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order
ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;


/*--HAC2*/

SELECT
'HAC2' AS Measure,
dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HAC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID
  and acc.DateDischargeKey = dat.DateKey
  and diag.DiagnosisKey = ddiag.DiagnosisKey
  and diag.Line ne 1
  AND diag.DX_POA_YNU IN ('N','U')
  AND acc.DateDischargeKey >= &BegDate
  AND acc.DateDischargeKey <= &EndDate
  AND ddiag.ICD9_Code IN (
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC2')  /*--('999.1')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order
ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;

quit


Accepted Solutions
Solution
‎06-17-2014 06:10 PM
Super Contributor
Posts: 307

Re: PROC SQL

You could use a UNION or UNION ALL statement to append the tables using PROC SQL. The difference between UNION and UNION ALL is that UNION ALL will return duplicates if they exist. In the following example, I've used UNION ALL. Note I haven't checked to make sure that the number of columns and column names in both SELECT statements are the same (they must be the same). Code is untested.

PROC SQL;
CREATE TABLE tempHAC AS

SELECT
'HAC1' AS Measure,

dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HaC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID and
       acc.DateDischargeKey = dat.DateKey and
    diag.DiagnosisKey = ddiag.DiagnosisKey and
diag.Line ne 1 and
diag.DX_POA_YNU IN ("N","U") and
acc.DateDischargeKey >= &BegDate
AND acc.DateDischargeKey <= &EndDate

AND ddiag.ICD9_Code IN ( 
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC1')/*--('998.4','998.7')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order

UNION ALL

SELECT
'HAC2' AS Measure,
dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HAC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID
  and acc.DateDischargeKey = dat.DateKey
  and diag.DiagnosisKey = ddiag.DiagnosisKey
  and diag.Line ne 1
  AND diag.DX_POA_YNU IN ('N','U')
  AND acc.DateDischargeKey >= &BegDate
  AND acc.DateDischargeKey <= &EndDate
  AND ddiag.ICD9_Code IN (
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC2')  /*--('999.1')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order
ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;

quit;

View solution in original post


All Replies
Super User
Posts: 17,960

Re: PROC SQL

What exactly do you want. That second proc doesn't make sense, why do you have create table and insert into.

Super Contributor
Posts: 1,040

Re: PROC SQL

Hi,

Thanks for the reply

I made the corrections to my code.

Even after removing the create table and insert into ...........I just get the results of HAC1 ...I am missing results of HAC2 from the  tempHAC dataset

Super User
Posts: 17,960

Re: PROC SQL

I suggest creating two separate tables and then appending them via proc append or proc sql union or a data step.

data want;

set hac1 hac2;

run;

Super Contributor
Posts: 1,040

Re: PROC SQL

Okie.

so we had to create tables sperately and then append them!!!

I cant do for example HAC1-HAC10 together in PROC SQL under one create statement??

IS that right??

Thanks

Super User
Posts: 17,960

Re: PROC SQL

You can, but you'd have to rework your query which is more work than *I* feel like doing. Perhaps someone else can help Smiley Happy

Solution
‎06-17-2014 06:10 PM
Super Contributor
Posts: 307

Re: PROC SQL

You could use a UNION or UNION ALL statement to append the tables using PROC SQL. The difference between UNION and UNION ALL is that UNION ALL will return duplicates if they exist. In the following example, I've used UNION ALL. Note I haven't checked to make sure that the number of columns and column names in both SELECT statements are the same (they must be the same). Code is untested.

PROC SQL;
CREATE TABLE tempHAC AS

SELECT
'HAC1' AS Measure,

dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HaC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID and
       acc.DateDischargeKey = dat.DateKey and
    diag.DiagnosisKey = ddiag.DiagnosisKey and
diag.Line ne 1 and
diag.DX_POA_YNU IN ("N","U") and
acc.DateDischargeKey >= &BegDate
AND acc.DateDischargeKey <= &EndDate

AND ddiag.ICD9_Code IN ( 
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC1')/*--('998.4','998.7')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order

UNION ALL

SELECT
'HAC2' AS Measure,
dat.Cal_Yr,
dat.Cal_Mo_Order,
COUNT(acc.HospitalAccountID) AS HAC_Count
FROM dwaprv.FACT_HospitalAccount AS acc,
dwaprv.FACT_HospitalAccountDiagnosis AS Diag,
dwaprv.DIM_Date AS dat,
dwaprv.DIM_Diagnosis AS ddiag
where acc.HospitalAccountID = diag.HospitalAccountID
  and acc.DateDischargeKey = dat.DateKey
  and diag.DiagnosisKey = ddiag.DiagnosisKey
  and diag.Line ne 1
  AND diag.DX_POA_YNU IN ('N','U')
  AND acc.DateDischargeKey >= &BegDate
  AND acc.DateDischargeKey <= &EndDate
  AND ddiag.ICD9_Code IN (
SELECT hacd.DX_Code
FROM dwaprv.REF_HACDiagnosis AS hacd
WHERE hacd.HAC_Description = 'HAC2')  /*--('999.1')*/
AND EncounterTypeKey IN (1, 12, 13, 14, 15)
GROUP BY dat.Cal_Yr, dat.Cal_Mo_Order
ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;

quit;

Super User
Super User
Posts: 6,502

Re: PROC SQL

Did you try just combining them into one statement?

Replace these lines:


ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;


with


UNION

Super User
Super User
Posts: 6,502

Re: PROC SQL

It looks like you are running the same query twice and are only modifying the value of the first variable and the WHERE clause.

If that is true then why do you need two queries at all?

PROC SQL;

CREATE TABLE tempHAC AS

  SELECT hacd.HAC_Description AS Measure

       , dat.Cal_Yr

       , dat.Cal_Mo_Order

       , COUNT(acc.HospitalAccountID) AS HaC_Count

  FROM dwaprv.FACT_HospitalAccount AS acc

     , dwaprv.FACT_HospitalAccountDiagnosis AS Diag

     , dwaprv.DIM_Date AS dat

     , dwaprv.DIM_Diagnosis AS ddiag

     , dwaprv.REF_HACDiagnosis AS hacd

  where acc.HospitalAccountID = diag.HospitalAccountID

    and acc.DateDischargeKey = dat.DateKey

    and diag.DiagnosisKey = ddiag.DiagnosisKey

    and diag.Line ne 1

    and diag.DX_POA_YNU IN ("N","U")

    and acc.DateDischargeKey >= &BegDate

    and acc.DateDischargeKey <= &EndDate

    and ddiag.ICD9_Code = hacd.DX_Code

    and hacd.HAC_Description in ('HAC1','HAC2')

    and /* ?? Which Table ?? */ EncounterTypeKey IN (1, 12, 13, 14, 15)

  GROUP BY 1,2,3

  ORDER BY 1,2,3

;

quit;

Super Contributor
Posts: 1,040

Re: PROC SQL

Hi Tom,

It was very good to know the technique.

But In my case from HAC1-HAC10 the conditions differ.

In the above example HAC1 and HAC2 includes only Diagnosis codes difference between them

Code was lengthy so I could not fit them all in.....

there are two tables: REF_HACDiagnosis tables containing Diagnoses ranging from HAC1-HAC10...also we have REF_HACProcedure tables containing Procedures labeled as values from HAC1 -HAC10 also

for some HACS only Diagnoses was considered (like shown above) and some HACS had to take into account both Diagnoses and Procedures

I think UNION ALL is the key take home message!!!

Also what is the meaning of GROUP BY 1 2 3????

Please correct

Regards

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 323 views
  • 4 likes
  • 4 in conversation