BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Fugue
Quartz | Level 8

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

9 REPLIES 9
Reeza
Super User

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

robertrao
Quartz | Level 8

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

Reeza
Super User

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;

robertrao
Quartz | Level 8

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

Reeza
Super User

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

Fugue
Quartz | Level 8

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;

Tom
Super User Tom
Super User

Did you try just combining them into one statement?

Replace these lines:


ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;


with


UNION

Tom
Super User Tom
Super User

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;

robertrao
Quartz | Level 8

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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