distinct statement

[Sandeep77]

Hi Experts,

I am trying to find the distinct phone numbers but getting sytax error. Could you check and let me know what is causing this error?

Thank you 

proc sql;
create table Tele_numbers as 
select a.*,
distinct b.tnphone as telephone,
case when
b.tnphone is not null then 'Y' else 'N'
end as Number_available
from Repcodes as a
Inner join
p2scflow.telephonenumbers as b on a.Accounts_number = b.tnkey;
quit;

Error:


29         proc sql;
30         create table Tele_numbers as
31         select a.*,
32         
33         distinct b.tnphone as telephone,
                    _
                    22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

34         case when
35         b.tnphone is not null then 'Y' else 'N'
36         end as Number_available
37         from Repcodes as a
38         Inner join
39         p2scflow.telephonenumbers as b on a.Accounts_number = b.tnkey;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
40         quit;

[Tom]

The DISTINCT keyword applies to the entire observation.  Place it before the variable list, not in the middle.

[LinusH]

Also, adding a.* would probably give a lot more records than you desire, if you want a neat least of unique phone numbers.