Search code examples
sqlsasenterprise-guide

Getting duplicate values in sas inspite of using distinct, how can I delete them?

OUTPUT OF CODEPROGRAM(Appending two tables and storing it in the later one) :

proc sql;
   create table tdstagng.aa as
   select distinct * from tdstagng.aa
   outer union corr
   select distinct * from WORK.DYNAMIC
   ORDER by Cdate;`
quit;

OUTPUT

This image is the output of the code, Cdate is the column that fetches the current date.) The goal of program is to build a historical data to mark changes with time.

In the date 10/15/18 there are duplicate values eventhough the whole row is same (instead of 7 rows for that date there are 14 rows), how do I get rid of this? I have marked with arrows the duplicated rows.

Solution

  • You selected two distinct sets and then concatenated them instead of just unioning them. That is what is leading to duplicate rows.

    Did you try removing the outer keyword? Here is example using SASHELP.CLASS data.

    23   proc sql ;
24   create table test1 as
25   select * from sashelp.class where name like 'A%'
26   union corr
27   select * from sashelp.class where sex = 'F'
28   ;
NOTE: Table WORK.TEST1 created, with 10 rows and 5 columns.

29   create table test2 as
30   select * from sashelp.class where name like 'A%'
31   outer union corr
32   select * from sashelp.class where sex = 'F'
33   ;
NOTE: Table WORK.TEST2 created, with 11 rows and 5 columns.

    Or making a sub-query?

    45
46   create table test3 as
47   select distinct * from
48   (
49   select * from sashelp.class where name like 'A%'
50   outer union corr
51   select * from sashelp.class where sex = 'F'
52   )
53   ;
NOTE: Table WORK.TEST3 created, with 10 rows and 5 columns.