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

[DataScientist]

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;

[LinusH]

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.

[DataScientist]

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.

[PGStats]

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.

[DataScientist]

@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.

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;

[PGStats]

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

[DataScientist]

Thanks very much for the clarification @PGStats.

[DataScientist]

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

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;

[Tom]

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.

[DataScientist]

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

[Tom]

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

[DataScientist]

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.

[ballardw · Accepted solution]

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.

[DataScientist]

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.