topic Re: PROC SQL in SAS Procedures

PROC SQL

robertrao — Tue, 17 Jun 2014 20:33:29 GMT

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

Re: PROC SQL

Reeza — Tue, 17 Jun 2014 20:42:53 GMT

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

Re: PROC SQL

robertrao — Tue, 17 Jun 2014 20:52:41 GMT

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

Re: PROC SQL

Reeza — Tue, 17 Jun 2014 20:56:39 GMT

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;

Re: PROC SQL

robertrao — Tue, 17 Jun 2014 20:59:51 GMT

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

Re: PROC SQL

Reeza — Tue, 17 Jun 2014 21:04:42 GMT

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

Re: PROC SQL

Fugue — Tue, 17 Jun 2014 22:10:37 GMT

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,

UNION ALL

quit;

Re: PROC SQL

Tom — Tue, 17 Jun 2014 23:41:24 GMT

Did you try just combining them into one statement?

Replace these lines:

ORDER BY dat.Cal_Yr, dat.Cal_Mo_Order
;

with

UNION

Re: PROC SQL

Tom — Tue, 17 Jun 2014 23:59:02 GMT

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;

Re: PROC SQL

robertrao — Wed, 18 Jun 2014 16:47:45 GMT

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