unique function in proc sql

[bochen]

Hi all,

The question is when I run the following code:

proc sql;
select household, unique(trip) as trip, sum(exp) 
from temp.data
group by household, trip;
quit;

SAS pop up the error: Function UNIQUE could not be located. However, when I switched the unique() term to the first and change the code to 

proc sql;
select unique(trip) as trip, household,  sum(exp) 
from temp.data
group by household, trip;
quit;

The procedure works normally.

So does this mean that unique() function needs to immediately follow select?

Thanks 

[Reeza · Accepted solution]

Unique in SAS implementation of SQL is analogous to DISTINCT, and it does apply to all columns and is required at the beginning of the SELECT statement.

Keyword: UNIQUE Context: [PROC SQL, CREATE TABLE STATEMENT] UNIQUE option
[constraint] Specifies that the values of the column must be unique. This constraint is identical to DISTINCT.

A caveat to that is when using in aggregate function such as count:

proc sql;
create table test5 as
select origin, count(distinct make) as count
from sashelp.cars
group by origin;
quit;