Omission of AS keyword in PROC SQL - new column name _TEMA001

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

Omission of AS keyword in PROC SQL - new column name _TEMA001

Hi,

 

The omission of the AS keyword in PROC SQL causes the new column name to be _TEMA001. Does TEMA stand for Tivoli Enterprise Monitoring Agent or does it have some other significance in SAS?

 

 

proc sql;
create table work.birth_months as
select Employee_ID, Birth_Date,
month(Birth_Date) /*as Birth_Month*/,
Employee_Gender
from orion.employee_information;
describe table work.birth_months;
select * from work.birth_months;
quit;

 

 


Accepted Solutions
Solution
‎08-15-2016 12:05 PM
Super User
Posts: 10,458

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

I'm going to guess that the _tem is actually related to the missingness of AS. Note that different ways of not using as create different A or G and possibly other suffixes. And that there is something aparently keeping track of the temporary variables created as in this example code:

proc sql;
   create table work.junk as
   select (age*3),(weight*3)
   from sashelp.class;
quit;

proc sql;
   create table work.junk as
   select mean(age*3),(weight*3)
   from sashelp.class;
quit;

the WEIGHT*3 gets two different names.

 

View solution in original post


All Replies
Super User
Posts: 5,254

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

I definitely don't think that SAS names columns after a product from another vendor.
Even if the name isn't random I don't think it has little significance for the SAS user.
Best practice must be to always name your columns.
Data never sleeps
Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

I agree @LinusH, I make sure I name my columns. In the off chance that I did not, though, got me wondering whether TEMA was an acronym or random letters strung together.

Respected Advisor
Posts: 4,640

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

To be exact, it is not the absence of the AS keyword that causes the creation of _TEMA001 column name, it is the omission of an alias name. The AS keyword itself is optional.

PG
Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

@PGStats, I know the AS keyword is optional in Transact-SQL. However, when PROC SQL code without the AS keyword is executed a syntax error occurs.

 

Alias.PNG

proc sql;
create table work.birth_months as
select Employee_ID, Birth_Date,
		month(Birth_Date) Birth_Month,
		Employee_Gender
	from orion.employee_information;
describe table work.birth_months;
select * from work.birth_months;
quit;
Respected Advisor
Posts: 4,640

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

My error. AS is optional for table aliases, but not for column names.

PG
Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

Thanks very much for the clarification @PGStats.

Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

Just tried omitting the AS for a table alias @PGStats. Please see a screenshot of the log.

 

Table Alias.PNG

 

proc sql;
create table work.birth_months /*as*/
select Employee_ID, Birth_Date,
		month(Birth_Date) 
		as Birth_Month,
		Employee_Gender
	from orion.employee_information;
describe table work.birth_months;
select * from work.birth_months;
quit;
Super User
Super User
Posts: 6,495

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

[ Edited ]

That is not a table alias. A table alias is defined immediately following the table name when it is referenced in a FROM clause.  The AS keyword is optional in that position.  For example run this code:

 

proc sql;
  select a.name,b.height
  from sashelp.class as a
     , sashelp.class b
  where a.name = b.name
  ;
quit;

The AS keyword after the CREATE TABLE clause is required when creating the new table directly from the results of a query.

Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

Thanks for your input @Tom. This clarifies the confusion regarding aliases.

Super User
Super User
Posts: 6,495

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

 

If you ran a statement like this to create a new table using a calculated value:

create table x as select mean(age) from sashelp.class;

Most SQL implementations would give an error that you did not give a name to the calculated column. Especially if MEAN(AGE) was not a valid value to use as a name.

 

But PROC SQL will allow you to not do that, but it will use the internally generated name for the column, as you saw.

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    _TEMG001    Num       8

 

Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

[ Edited ]

This is good to know @Tom. In an upcoming engagement, I will need to use T-SQL so the differences between PROC SQL and other SQL implementations will come in handy.

Solution
‎08-15-2016 12:05 PM
Super User
Posts: 10,458

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

I'm going to guess that the _tem is actually related to the missingness of AS. Note that different ways of not using as create different A or G and possibly other suffixes. And that there is something aparently keeping track of the temporary variables created as in this example code:

proc sql;
   create table work.junk as
   select (age*3),(weight*3)
   from sashelp.class;
quit;

proc sql;
   create table work.junk as
   select mean(age*3),(weight*3)
   from sashelp.class;
quit;

the WEIGHT*3 gets two different names.

 

Contributor
Posts: 49

Re: Omission of AS keyword in PROC SQL - new column name _TEMA001

Great insight @ballardw. So _TEM is the prefix and then the suffix could be an A or G (or maybe another alphabet depending on the operation performed) followed by a number.

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 722 views
  • 6 likes
  • 5 in conversation