topic Re: Automatic Variable in proc sql in SAS Programming

Automatic Variable in proc sql

FK1 — Mon, 13 Jan 2020 13:57:18 GMT

Hi Everyone,

running this code

data class_modified;
set sashelp.class;
rename name = User;
run;

proc sql;
create table work.test as select distinct
User 
from work.class_modified;
quit;

I get as a result from the PROC SQL-Query the "wrong" result (I get my SAS-userid displayed rather than the names of the sashelp.class data set variable "name") becasue the Enterprise Guide parser "thinks" that I want to use the automatic variable "User" rather than the actual variable I renamed.

Alread the syntax highlightning "tells" me this, as it displays the variable in blue rahter than as a black word:

When using a different procedure, e.g. PROC SORT, this does not happen (also the highlightning ist correct) :

Why does this happen?

Re: Automatic Variable in proc sql

JeffMaggio — Mon, 13 Jan 2020 14:14:10 GMT

The code you posted works fine for me with one minor adjustment.

If I were you, I would change the variable name as SAS reads the data in (on the set statement), though either way should work.

data class_modified;
set sashelp.class (rename=(name = User));
run;

For the second step, just explicitly tell SAS to use the User variable from the table:

proc sql;
create table work.test as select distinct
a.User 
from work.class_modified a;
quit;

Re: Automatic Variable in proc sql

FK1 — Mon, 13 Jan 2020 14:18:16 GMT

Thank's for your reply, @JeffMaggio . As you pointed out, I could use an explicit ALIAS. I could also tell SAS to use the name literal 'User'n, which also works. I am just confused, as to why it is neccessary in PROC SQL and not necessary in other procedures like PROC SORT, for example....

Re: Automatic Variable in proc sql

Tom — Mon, 13 Jan 2020 14:22:15 GMT

USER is a special keyword in PROC SQL. To eliminate the confusion between this special meaning and a reference to a specific variable you can use an alias in your reference to the variable. Something like this:

select a.user from mytable a ;

Re: Automatic Variable in proc sql

JeffMaggio — Mon, 13 Jan 2020 14:24:00 GMT

User is a reserved keyword in SQL. The same thing would happen if you have a column named 'from', 'select', etc.

Re: Automatic Variable in proc sql

Tom — Mon, 13 Jan 2020 14:54:23 GMT

@JeffMaggio wrote:

User is a reserved keyword in SQL. The same thing would happen if you have a column named 'from', 'select', etc.

That is only partly right. USER is not the same type of keyword as FROM or SELECT.

SAS will happily allow you to use FROM or SELECT as the name of a variable since it can tell from the position in the statement the role the word is playing. But since USER is the name of a metadata variable (the username that is running the query) it is used in the same context as a normal variable name. If you want to reference a variable named USER you just need to add some extra "syntactic sugar" so that the compiler knows which USER you want.

proc sql ;
select
   user as username
  ,class.user as student_name
  ,select as student_age
  ,from  as student_height
from sashelp.class(obs=2 rename=(name=user height=from age=select))
;