Checking for Duplicates using multiple variables

[spayne16]

Hey all,

I'm a very new user attempting to move some of my work from SQL to SAS.  One thing I constantly do is check for duplicates.  In this example, I'm trying to create a table with the fields MedGroup, RGN_CD, CLM_NB, and a count of the CLM_NB field I call CLMCount.  I only want to see rows where the CLMCount is greater than 1.  Basically, does this Claim Number show up on multiple lines with the same MedGroup and RGN_CD?  SAS is not recognizing the CLMCount that I reference later on.  I'm fine using a proc freq or any other command to get this, but nothing I try seems to make it work (and I'm an extreme novice). Thanks for your help!

Proc Sql;
Create Table PROF_Dup_Test as
Select MedGroup, RGN_CD, CLM_NB, Count(CLM_NB) as CLMCount
From PROF_Grouped
Where CLMCount>1
Group By MedGroup, RGN_CD, CLM_NB
Order by CLMCount desc;
Quit;

[SASKiwi · Accepted solution]

Try this:

Proc Sql;
  Create Table PROF_Dup_Test as
  Select MedGroup, RGN_CD, CLM_NB, Count(CLM_NB) as CLMCount
  From PROF_Grouped
  Group By MedGroup, RGN_CD, CLM_NB
  Having CLMCount>1
  Order by CLMCount desc;
Quit;

[spayne16]

That worked perfectly!  Thank you!!

[ballardw]

---

@spayne16 wrote:

Hey all,

I'm a very new user attempting to move some of my work from SQL to SAS.  One thing I constantly do is check for duplicates.  In this example, I'm trying to create a table with the fields MedGroup, RGN_CD, CLM_NB, and a count of the CLM_NB field I call CLMCount.  I only want to see rows where the CLMCount is greater than 1.  Basically, does this Claim Number show up on multiple lines with the same MedGroup and RGN_CD?  SAS is not recognizing the CLMCount that I reference later on.  I'm fine using a proc freq or any other command to get this, but nothing I try seems to make it work (and I'm an extreme novice). Thanks for your help!

Proc Sql;
Create Table PROF_Dup_Test as
Select MedGroup, RGN_CD, CLM_NB, Count(CLM_NB) as CLMCount
From PROF_Grouped
Where CLMCount>1
Group By MedGroup, RGN_CD, CLM_NB
Order by CLMCount desc;
Quit;

---

Try

Proc Sql;
   Create Table PROF_Dup_Test as
   Select MedGroup, RGN_CD, CLM_NB, Count(CLM_NB) as CLMCount
   From PROF_Grouped
   Group By MedGroup, RGN_CD, CLM_NB
   Having calculated CLMCount>1
   Order by CLMCount desc;
Quit;

The Calculated may not be needed but is often needed for something calculated in a proc step.

The HAVING is needed instead of Where to get something related to the "group by"

[spayne16]

Ah got it - thanks for explaining the switch to having and the calculated part!  This was super helpful!!