Desktop productivity for business analysts and programmers

Column names using libname with Excel

Reply
Contributor
Posts: 24

Column names using libname with Excel

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

Respected Advisor
Posts: 3,124

Re: Column names using libname with Excel

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

Respected Advisor
Posts: 3,063

Re: Column names using libname with Excel

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. 

Grand Advisor
Posts: 17,360

Re: Column names using libname with Excel

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

Contributor
Posts: 24

Re: Column names using libname with Excel

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

Grand Advisor
Posts: 17,360

Re: Column names using libname with Excel

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;

Contributor
Posts: 24

Re: Column names using libname with Excel

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

Ask a Question
Discussion stats
  • 6 replies
  • 243 views
  • 0 likes
  • 4 in conversation