reading in data sets with spaces in variable labels, sas 9.4 vs enterprise 6.1

[geneshackman]

Question. I have a program, I ran in SAS 9.4, ran okay, then ran in enterprise 6.1, and it did NOT run.

One part of the sas program has proc sql, combining some data sets. One of the data sets was in excel and a couple of variables had space in their names, e.g., "index name". In sql, the variable is referred to as "index_name".

SAS 9.4 read this excel data set in, changing the variable name to "index_name". Enterprise apparently does not change the name to have an underscore instead of a space. When I ran the same program in enterprise, the error message said, when running the sql lines, it could not find the variable "index_name".

Anyone run into this before? What to do so that I can run the same program in base sas 9.4 -and- in enterprise?

Thanks

[Haikuo]

Try adding one line on top of your code:

options validvarname=any;

[geneshackman]

Thanks for the suggestion.

However, that says to sas that "index name" is a valid variable name, right? But then as I mentioned the sql code uses the name "index_name", with an underscore. SAS 9.4 automatically changes the name of the variable to include an underscore. So now, using validvarname, will the sql, running in SAS 9.4, also say it can't find "index_name"?

I tried to change the name of the variable "index name" to "index_name" but couldn't seem to do it. I also tried running the sql with a space in the name but that caused a problem too.

I think the solution is to change the name of the variable "index name" to "index_name". But how, exactly, to do that?

Thanks

[Haikuo]

"However, that says to sas that "index name" is a valid variable name, right?", not quite. You would need to use:

'index name'n to refer it.

[geneshackman]

like in this response?

https://communities.sas.com/t5/SAS-Enterprise-Guide/dealing-with-space-in-variable/td-p/136173

I tried that before, didn't work, but I'll try again, now that I'm starting with the variable name with a space in it.

[Haikuo]

correct. N literals has to be paried with

options validvarname=any;

Put this line on top of your code, try again. Good luck!

[geneshackman]

This may be the correct solution, but for some reason adding in your code is causing too many other problems in other places. I don't know why. So for the present, I will stick with my solution, running the program in SAS 9.4 and just not running this particular program in enterprise. Maybe some other time I'll see if I can figure it out. Thanks though.

[Haikuo]

My guess is that all of your downstream 'index_var' will have to be converted to 'index var'n style.

[Patrick · Accepted solution]

SAS code as such should execute the same way and return the same result independent whether you run it via "PC SAS" or SAS EG.

SAS EG is the client, the actual code gets executed on the server. If you're really only running copy/pasted code out of EG then something in the environment must be different (eg. options).

As I understand it, you want actually the import step to convert your variable names to valid SAS variable names by replacing stuff like blanks with underscores.

How do you import the Excel data in EG? If you're using Proc Import then set option validvarname to V7 and NOT to ANY

OPTIONS VALIDVARNAME=V7; as this will trigger Proc Import to convert source column names to valid SAS variable names.

http://support.sas.com/documentation/cdl/en/lesysoptsref/69799/HTML/default/viewer.htm#p124dqdk8zoqu...

One thing SAS EG does (which I don't like): It adds some code before and after your own code before sending it to the server for execution. I assume the intention is to make things for us even more intutitive - but sometimes that goes wrong. I've just checked in my current EG version. Setting option validvarname is one of these things EG does without asking.

You can code the option setting at the beginning of your code as this will overwrite any SAS EG setting, or you can change in the EG options what EG generates at the top of your code (may be best is to select "use setting from server").