topic Re: Column names using libname with Excel in SAS Enterprise Guide

Column names using libname with Excel

mrom34 — Wed, 20 Mar 2013 00:46:14 GMT

Hi,

I am trying to import an excel file using libname on EG. It works fine, I can connect to the file and the using a data step I can just create a table. However, it creates SAS data sets with columns names that do not comply to SAS naming conventions. I get columns with names like 'sample ID 1'. How am I suppose to use these variable names in a SAS code?

I am currently moving from display manager to EG and I am not how to solve this issue.

thanks for your help

Romain

Re: Column names using libname with Excel

Haikuo — Wed, 20 Mar 2013 01:17:41 GMT

Those 'sample ID 1' you have mentioned probably are just labels. The actual name is more likely to be 'sample_ID_1'. You can run proc content to confirm or just double click the column name after you have the table opened up.

Haikuo

Re: Column names using libname with Excel

SASKiwi — Wed, 20 Mar 2013 02:03:34 GMT

Try the SAS option: options validvarname = V7; It will ensure that your libname translates your Excel columns to SAS columns. The default for EG is validvarname = ANY, which produces the symptoms you are seeing.

Re: Column names using libname with Excel

Reeza — Wed, 20 Mar 2013 02:17:15 GMT

You can refer to them as 'Sample ID 1'n or try changing the options to get a valid SAS var name.

Re: Column names using libname with Excel

mrom34 — Wed, 20 Mar 2013 16:45:04 GMT

Thanks for your suggestion. options validvarname = V7 seems to work but it renames the columns _col0,_col1,_col2. I wish it would just replace the blanks with underscore.

I still don't know how I can use a data set where the column name is 'Sample ID 1' ( I checked with proc content and it is actually the variable name). these data sets cannot be opened with SAS base

Re: Column names using libname with Excel

mrom34 — Wed, 20 Mar 2013 16:53:10 GMT

Okay I think I found the solution, I just use this option: options validmemname=extend;

Re: Column names using libname with Excel

Reeza — Wed, 20 Mar 2013 17:06:43 GMT

In SAS BASE you then need to specify the options to use the datasets from EG. Then to reference the variable, say sample id 1 you quote it and put an n as I indicated earlier.

options validvarname=any;

data want;

set have;

sample_id1='Sample ID 1'n;

Run;