topic Not able to extract fields from oracle with number as initial character in SAS Programming

Not able to extract fields from oracle with number as initial character

deleted_user — Thu, 30 Apr 2009 06:35:24 GMT

I am using a select * query to extract data from oracle to create a sas dataset.
The problem is, oracle is containing column name with number as initial character or special characters.In the SAS dataset the numebr or special characters are replaced by "_".For e.g. If oracle field name is 1employee* then in SAS dataset the field name will be _employee_.
Things that I have tried out:
1. I have used the option validvarname = any.It gives warning which is not acceptable.
2. SYSERR cannot be used as I am using it to log error message.
3. Printing a part of log to other log also doesnt help.

Please provide some suggestion.
Thanks in advance

Re: Not able to extract fields from oracle with number as initial character

sbb — Thu, 30 Apr 2009 18:42:11 GMT

Just curious - what problem are you looking to solve given the SAS system requirement for a non-numeric first char for SAS variables? Best to explain your INPUT and desired OUTPUT condition, with sample data, similar to what you have.

Scott Barry
SBBWorks, Inc.

Re: Not able to extract fields from oracle with number as initial character

deleted_user — Mon, 04 May 2009 04:37:55 GMT

There are two problems that I am facing:

1. Input column name from oracle : 1employee
Currently SAS gives output like : _employee
Expected Result : 1employee

2. Input column name from oracle : emp*loyee
Currently SAS gives output like : emp_loyee
Expected Result : emp*loyee

By using option validvarname = any the problem gets solved, but it gives warning in log. I dont want that.

Re: Not able to extract fields from oracle with number as initial character

sbb — Mon, 04 May 2009 12:51:54 GMT

Given the SAS architecture limitation, I do not believe you have a choice with naming columns, and more accurately, the problem is circumvented, not solved, with the SAS OPTIONS setting VALIDVARNAME=.

From the SAS support http://support.sas.com/ website, I found some SAS-host DOC reference and also a SAS conference paper on the topic.

Scott Barry
SBBWorks, Inc.

SAS System Option VALIDVARNAME=

http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000279245.htm

Avoiding Common Traps When Accessing RDBMS Data
Mike Rhoads, Westat, Rockville, MD
http://support.sas.com/resources/papers/proceedings09/141-2009.pdf