Search code examples
sqlsas

How to Distinct one column from all the column in SAS SQL

[enter image description here]

1

For Example in below table, I need output with same name and ID and different code

Name    ID  Code
Tom     1   155
Tom     1   165
Tom     2   166
Tom     2   166
Tom     3   128
Tom     4   999

Below is the required Output

Name    ID  Code
Tom     1   155
Tom     1   165
Solution

  • Use GROUP BY and HAVING.

    First let's convert your example listing into an actual dataset.

    data have; 
 input Name $ ID Code;
cards;
Tom     1   155
Tom     1   165
Tom     2   166
Tom     2   166
Tom     3   128
Tom     4   999
;

    So group by NAME and ID and only keep the cases where there are more than one distinct value of CODE.

    proc sql;
  create table want as 
    select *
    from have 
    group by name,id
    having count(distinct code) > 1
  ;
quit;

    Results:

    Obs    Name    ID    Code

 1     Tom      1     165
 2     Tom      1     155