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

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

DataScientist — Sat, 13 Aug 2016 07:20:26 GMT

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;

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

LinusH — Sat, 13 Aug 2016 07:36:07 GMT

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.

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

DataScientist — Sat, 13 Aug 2016 07:59:19 GMT

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.

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

PGStats — Sat, 13 Aug 2016 18:01:13 GMT

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.

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

DataScientist — Sat, 13 Aug 2016 19:33:59 GMT

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

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

PGStats — Sat, 13 Aug 2016 19:47:44 GMT

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

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

DataScientist — Sun, 14 Aug 2016 10:53:25 GMT

Thanks very much for the clarification @PGStats.

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

DataScientist — Sun, 14 Aug 2016 11:52:20 GMT

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;

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

Tom — Sun, 14 Aug 2016 15:00:39 GMT

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.

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

Tom — Sun, 14 Aug 2016 15:43:50 GMT

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

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

DataScientist — Mon, 15 Aug 2016 04:03:55 GMT

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

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

DataScientist — Mon, 15 Aug 2016 04:16:01 GMT

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.

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

ballardw — Mon, 15 Aug 2016 14:25:17 GMT

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.

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

DataScientist — Mon, 15 Aug 2016 16:04:11 GMT

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.