DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 0

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

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
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
N/A
Posts: 0

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

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.
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 3 replies
  • 146 views
  • 0 likes
  • 2 in conversation