Validvarname Giving Generic Variable References

[Lost_Gary]

I am using data from multiple sources that is not consistent.  This source furnishes information in Excel format.  Because my variable names are not universal I have been using the Validvarname option to import the data (using proc import, getnames=yes).  For some fields this returns a generic value, such as VAR1, VAR2, etc.  Is there anyway to get this to return a value?  

For instances, I have a variable called "Sample".  One source has listed this as "Sample (Y/N)".  This returns the generic value of VAR15 and I would like some form of the word "Sample" so i can understand the content.    

Any ideas are appreciated.  

Thanks

[SuryaKiran]

Hello,

Validvarname=any option should read your column names that have special characters.

options validvarname=any;

[Tom]

Why not just look at the LABEL on the variable? 

PROC IMPORT from an XLSX file should put the original header value into the LABEL no matter what setting you have for the VALIDVARNAME option.

[Reeza]

Are the tables generally the same? Is there an option to convert, in batch, to csv and then read those in with the desired specifications. 

If so, here's a vb script that will convert all files in a folder from xlsx to csv. 

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

---

@Lost_Gary wrote:

I am using data from multiple sources that is not consistent.  This source furnishes information in Excel format.  Because my variable names are not universal I have been using the Validvarname option to import the data (using proc import, getnames=yes).  For some fields this returns a generic value, such as VAR1, VAR2, etc.  Is there anyway to get this to return a value?  

For instances, I have a variable called "Sample".  One source has listed this as "Sample (Y/N)".  This returns the generic value of VAR15 and I would like some form of the word "Sample" so i can understand the content.    

Any ideas are appreciated.  

Thanks

---

[Lost_Gary]

so the validvarname = any will pull the data into a dataset, but I can't call that data field based on the name "Sample (Y/N)" as that wouldn't be valid.  Obviously the next move I want to make is an if statement such as If Sample = 'Y' then ........

Unfortunately, the data is probably not consistent enough to simply switch to a csv, plus there is need for me to pull data from different sheets within each workbook.  

[ChrisNZ]

> I can't call that data field based on the name "Sample (Y/N)" 

You can.

You have to use the string suffix n.

The best way to use it is generally in a rename statement/option so you don't carry these silly names in your program.

rename 'Sample (Y/N)'n = SAMPLE;

Then set option validvarname back to V7.