Help using Base SAS procedures

unique function in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

unique function in proc sql

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 


Accepted Solutions
Solution
‎02-26-2016 10:10 AM
Super User
Posts: 19,870

Re: unique function in proc sql

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;

View solution in original post


All Replies
Solution
‎02-26-2016 10:10 AM
Super User
Posts: 19,870

Re: unique function in proc sql

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 1733 views
  • 0 likes
  • 2 in conversation