BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8

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

7 REPLIES 7
ballardw
Super User

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
Quartz | Level 8

Hi,

Thanks for the reply.

I am not getting any message in the log.

There is no Cartesian product either

Thanks

stat_sas
Ammonite | Level 13

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

COUNT (distinct acc.HospitalAccountID) AS HAC_Count

SASKiwi
PROC Star

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

robertrao
Quartz | Level 8

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
Super User Tom
Super User

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1014 views
  • 0 likes
  • 6 in conversation