topic New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM in SAS Programming

New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

Elliott — Thu, 27 Apr 2023 20:39:27 GMT

Hi,

I have a new sql server table data source that I need to query but several of the variables have hyphens in the variable names.

I am using a SAS pass through query to access the SQL Server database/view.

How do I get SAS to accept the non standard names? I plan on renaming to change the hyphen to an underscore but right now I cannot even read them in.

Any assistance will be greatly appreciated.

Thanks,

Elliott

Re: New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

SASKiwi — Thu, 27 Apr 2023 20:50:35 GMT

If you use this option SAS will automatically change the column names from hyphens to underscores when reading these tables:

options validvarname = V7;

If you need to refer to the original names in code then do this 'LETTER-REF-NUM'n

Re: New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

Elliott — Thu, 27 Apr 2023 21:45:20 GMT

I have tried options validvarname = V7; and options validvarname = any; neither work, still erroring with not valid SAS name.

Re: New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

Reeza — Thu, 27 Apr 2023 22:02:14 GMT

If you're using SQL passthru rename them when you pull them to valid SAS names.

Ie for Oracle

Select "First Name" as first_name,
.....
from customers;
quit;

Re: New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

Patrick — Thu, 27 Apr 2023 22:12:49 GMT

@Elliott wrote:

I have tried options validvarname = V7; and options validvarname = any; neither work, still erroring with not valid SAS name.

This should work. Please provide the code and log where you still encounter errors.

Options That Affect SAS/ACCESS Naming Behavior

Re: New SQL Data Source with hyphens in variable names ie LETTER-REF-NUM

Elliott — Thu, 27 Apr 2023 22:51:11 GMT

I was able to read in the data using "LETTER-REF-NUM"n

Thanks All for your help.

Elliott