BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

13 REPLIES 13
LinusH
Tourmaline | Level 20
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
DataScientist
Quartz | Level 8

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
Opal | Level 21

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
DataScientist
Quartz | Level 8

@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;
PGStats
Opal | Level 21

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

PG
DataScientist
Quartz | Level 8

Thanks very much for the clarification @PGStats.

DataScientist
Quartz | Level 8

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;
Tom
Super User Tom
Super User

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
Quartz | Level 8

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

Tom
Super User Tom
Super User

 

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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