BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bochen
Fluorite | Level 6

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

1 REPLY 1
Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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