BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
geneshackman
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

Capture.PNG

 

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

Try adding one line on top of your code:

 

options validvarname=any;
geneshackman
Pyrite | Level 9

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
Onyx | Level 15

"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
Pyrite | Level 9

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
Onyx | Level 15

correct. N literals has to be paried with

options validvarname=any;

 

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

geneshackman
Pyrite | Level 9

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
Onyx | Level 15

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

Patrick
Opal | Level 21

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

Capture.PNG

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5202 views
  • 3 likes
  • 3 in conversation