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
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;
What exactly do you want. That second proc doesn't make sense, why do you have create table and insert into.
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
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;
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
You can, but you'd have to rework your query which is more work than *I* feel like doing. Perhaps someone else can help
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;
Did you try just combining them into one statement?
Replace these lines:
ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;
with
UNION
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.