topic How to remove duplicates using Proc Sql in SAS Programming

How to remove duplicates using Proc Sql

SASuserlot — Thu, 17 Feb 2022 19:55:59 GMT

Hi, I am new to Proc SQL, I would like to know how we can remove the duplicates from dataset and achieve the exactly same thing that I can achieve through the PROC SORT. In my example I want achieve the output exactly looks like CLASS1 dataset using PROC SQL. I am trying with sql but having difficulty to achieve the same., I am able to get age, and sex but not the remaining variable. Can you please let me know what I am doing wrong. Thanks in advance.

data class;
set sashelp.class;
run;

proc sort data= class out= class1 nodupkey; by sex age ; run;

proc sql;
create table clsql as select distinct age,sex
from class  group by age,sex order by age,sex;
quit;

Re: How to remove duplicates using Proc Sql

ballardw — Thu, 17 Feb 2022 20:23:10 GMT

Since Proc Sort will create different datasets given a different order of the data I think you need to consider and describe in much more excruciating detail what your real use case may be.

The initial order of the data set in Proc sort with NODUPKEY will affect the result. Here is an example.

data class;
set sashelp.class;
run;

proc sort data= class out= class1 nodupkey; 
   by sex age ; 
run;

proc sort data=class;
   by height;
run;

proc sort data= class out= class2 nodupkey; 
   by sex age ; 
run;

If Class1 and Class2 are the same when you run this code I would be very surprised.

SQL has a very similar data order issue as it is not designed to process data in any given sequence. Sometimes with large data sets processing the same code on unchanged data sets will result in different results if order is considered.

Re: How to remove duplicates using Proc Sql

SASuserlot — Thu, 17 Feb 2022 20:38:58 GMT

Hi @ballardw . Thank you for response. I am not pro with SQL, So I am learning how I can I achieve the same that we can do by proc sort to know how we can avoid duplicates.

For your question, When I ran your code, I see Class1 and Class2 are not same.

Is it possible for you provide and example where we can remove the duplicates and achieve same using proc sort and proc sql, That will be greatly appreciated. I think class dataset may not be good .Thanks again.

Re: How to remove duplicates using Proc Sql

SASKiwi — Thu, 17 Feb 2022 21:07:47 GMT

You need to define a deduplication rule that precisely selects the rows you want so that it will work the same in PROC SORT and PROC SQL.

For example with the CLASS dataset you could say: create a table that contains the tallest student for each age and sex value combination. As long as the data has unique values for height (which I think it has), you now have a precise definition that you can code in PROC SORT and PROC SQL and you will get the same result.

Re: How to remove duplicates using Proc Sql

SASuserlot — Thu, 17 Feb 2022 22:02:26 GMT

Got it thank you. can you provide an example how to remove duplicates using the Proc sql, may at two variable level. I do have idea using 'distinct' for single variable level duplication removal. Thanks

Re: How to remove duplicates using Proc Sql

Kurt_Bremser — Thu, 17 Feb 2022 22:34:31 GMT

If you want the same result in SORT and SQL, you need to design a rule for which duplicate to select, and then you can force both to implement that rule. Without forcing a specific rule, the results will be indeterminate (even in SORT, depending on storage engines).

So we first need that rule.

Re: How to remove duplicates using Proc Sql

SASKiwi — Thu, 17 Feb 2022 23:22:49 GMT

Here is an SQL approach for the rule I previously described:

proc sql;
  create table want as
  select A.*
  from sashelp.class as A
  inner join
  (select  age
          ,sex 
          ,max(height) as Max_Height
   from sashelp.class
   group by age
           ,sex
  ) as B 
  on A.age = B.age
  and A.sex = B.sex
  and A.height = B.Max_Height
  order by sex, age
  ;
quit;

Re: How to remove duplicates using Proc Sql

SASuserlot — Fri, 18 Feb 2022 17:22:17 GMT

Thanks