topic Re: Find duplicated values in SAS EG in SAS Enterprise Guide

Find duplicated values in SAS EG

ralizadeh — Wed, 10 May 2023 23:24:30 GMT

Is there a way in SAS EG to find duplicated values? Must have duplicated rows in CIN and YY columns but have distinct MM. And I need all three columns in the output. Example data below:

Re: Find duplicated values in SAS EG

LinusH — Thu, 11 May 2023 08:19:00 GMT

If you want to this using UI rather than direct programming, try Query Builder, by defining CIN and YY as grouping variables, and with a having filter on count(*) > 1.

Re: Find duplicated values in SAS EG

ralizadeh — Thu, 11 May 2023 18:45:47 GMT

Thanks @LinusH. Just to confirm, did you mean that I should add CIN, YY, MM to the Select Data (columns) in the Query Builder, then use COUNT as a summary function for MM. Then add CIN, YY to the Group By. Add Count(*) >1 to HAVING clause under Filter tab?

Re: Find duplicated values in SAS EG

LinusH — Fri, 12 May 2023 13:42:35 GMT

I think so, I don't have access to EG right now so I can't verify.

But you should end up in a SQL similar to:

proc sql;
   select cin, yy, mm
   from have
   group by cin, yy
   having count(*) > 1;
quit;

Re: Find duplicated values in SAS EG

ralizadeh — Fri, 12 May 2023 22:40:21 GMT

Thanks @LinusH While my original goal was to identify the duplicate row values, your suggestion proved to be valuable in a broader sense. It allowed me to at least determine the count of rows with duplicate values.