topic Re: SQL Code in SAS Procedures

SQL Code

robertrao — Mon, 07 Jul 2014 22:21:07 GMT

Hi Team,

Could someone help me find the mistake I am doing in this SQL code????

I am getting the below output

my question is that when I group it by HAC which I unique I was expecting the HAC_count as 1 for al the records.

But to my surprise I am getting 2 .

could you please help me figure this????

MEASURE CAL_YR CAL_MO_ODER HospitalAccountID HAC_count

HAC4 2013 5 101 1

HAC4 2013 5 102 2

HAC4 2013 5 103 1

%let BegDate=20110101;

%let EndDate=20140707;

PROC SQL;

CREATE TABLE HAC4 AS

SELECT

'HAC4' AS Measure,

dat.Cal_Yr,

dat.Cal_Mo_Order,

acc.HospitalAccountID as Hac,

COUNT (acc.HospitalAccountID) AS HAC_Count

FROM dwprap.FACT_HospitalAccount AS acc,

dwprap.FACT_HospitalAccountDiagnosis AS diag,

dwprap.DIM_Date AS dat,

dwprap.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 dwprap.REF_HACDiagnosis AS hacd

WHERE hacd.HAC_Description = 'HAC4') /*--('707.23','707.24')*/

AND EncounterTypeKey IN (1, 12, 13, 14, 15)

GROUP BY HAC, dat.Cal_Yr, dat.Cal_Mo_Order

ORDER BY HAC, dat.Cal_Yr, dat.Cal_Mo_Order

;

quit;

Thanks

Re: SQL Code

ballardw — Mon, 07 Jul 2014 22:29:27 GMT

Are you getting any message in the log about cartesian products or such? The way you are joining the tables is likely to create such. So if if HAC is unique in one table each record is being matched agains all the other records in the other table creating duplications in the output.

Re: SQL Code

robertrao — Mon, 07 Jul 2014 22:37:46 GMT

Hi,

Thanks for the reply.

I am not getting any message in the log.

There is no Cartesian product either

Thanks

Re: SQL Code

stat_sas — Mon, 07 Jul 2014 23:12:55 GMT

Try changing this in the code to see how it works.

COUNT (distinct acc.HospitalAccountID) AS HAC_Count

Re: SQL Code

SASKiwi — Tue, 08 Jul 2014 01:44:38 GMT

Try adding acc.HospitalAccountID to your GROUP BY and ORDER BY

Re: SQL Code

robertrao — Tue, 08 Jul 2014 01:59:02 GMT

HI,

i was with the opinion since acc.hospitalaccountid was aliased as HAC I used it in the groupby and orderby!!!

does it need to be changed to hospitalaccountid itself???????????

thanks

Re: SQL Code

Tom — Tue, 08 Jul 2014 02:57:08 GMT

Were you surprised to find duplicate rows? I would expect that within the same year and month it is very likely to have more than one order for the same account.

Or did you mean to use COUNT (DISTINCT acc.HospitalAccountID) ?

Unless acc.HospitalAccountID has missing values then COUNT (acc.HospitalAccountID) is the same as COUNT(*).

Re: SQL Code

Ksharp — Tue, 08 Jul 2014 14:39:56 GMT

You didn't post your sample data. and I doubt there two duplicated obs for your 'unique' variable .