Help using Base SAS procedures

SQL Code

Reply
Super Contributor
Posts: 1,041

SQL Code

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

Super User
Posts: 11,343

Re: SQL Code

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: SQL Code

Hi,

Thanks for the reply.

I am not getting any message in the log.

There is no Cartesian product either

Thanks

Trusted Advisor
Posts: 1,228

Re: SQL Code

Posted in reply to robertrao

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

COUNT (distinct acc.HospitalAccountID) AS HAC_Count

Super User
Posts: 3,254

Re: SQL Code

Posted in reply to robertrao

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

Super Contributor
Posts: 1,041

Re: SQL Code

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

Super User
Super User
Posts: 7,050

Re: SQL Code

Posted in reply to robertrao

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

Super User
Posts: 10,028

Re: SQL Code

Posted in reply to robertrao

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

Ask a Question
Discussion stats
  • 7 replies
  • 294 views
  • 0 likes
  • 6 in conversation