SQL Code

[robertrao]

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

[ballardw]

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.

[robertrao]

Hi,

Thanks for the reply.

I am not getting any message in the log.

There is no Cartesian product either

Thanks

[stat_sas]

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

COUNT (distinct acc.HospitalAccountID) AS HAC_Count

[SASKiwi]

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

[robertrao]

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

[Tom]

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(*).

[Ksharp]

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