topic Re: Checking for Duplicates using multiple variables in New SAS User

Checking for Duplicates using multiple variables

spayne16 — Wed, 14 Apr 2021 21:49:40 GMT

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;

Re: Checking for Duplicates using multiple variables

SASKiwi — Wed, 14 Apr 2021 21:58:14 GMT

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;

Re: Checking for Duplicates using multiple variables

ballardw — Wed, 14 Apr 2021 21:58:57 GMT

@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"

Re: Checking for Duplicates using multiple variables

spayne16 — Wed, 14 Apr 2021 22:02:13 GMT

That worked perfectly! Thank you!!

Re: Checking for Duplicates using multiple variables

spayne16 — Wed, 14 Apr 2021 22:03:05 GMT

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