topic Re: Validvarname Giving Generic Variable References in SAS Programming

Validvarname Giving Generic Variable References

Lost_Gary — Thu, 24 Jan 2019 18:29:16 GMT

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

Re: Validvarname Giving Generic Variable References

SuryaKiran — Thu, 24 Jan 2019 18:41:07 GMT

Hello,

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

options validvarname=any;

Re: Validvarname Giving Generic Variable References

Tom — Thu, 24 Jan 2019 19:29:05 GMT

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.

Re: Validvarname Giving Generic Variable References

Reeza — Thu, 24 Jan 2019 19:32:03 GMT

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

Re: Validvarname Giving Generic Variable References

Lost_Gary — Thu, 24 Jan 2019 20:09:50 GMT

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.

Re: Validvarname Giving Generic Variable References

ChrisNZ — Thu, 24 Jan 2019 20:34:42 GMT

> 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.