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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.