topic Re: SAS studio import excel file issue in SAS Programming

SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 18:22:24 GMT

Hello,

The below is my code to import excel file with SAS studio. When I run step by step, the output variables had space, like 'Test A', 'Test B' (fig2 below). However, when I run all steps together, the output variables changed into "Test_A", 'Test_B' (fig. 3 below).

My question is how I set up my SAS studio or revise my code to get fig.3 output when running the code step by step. Thanks

		 options validvarname=v7;   /*Step 1*/
		 
		 proc import datafile="...\test.xlsx" out = test dbms=xlsx replace;  /*step 2*/
		 	getnames=yes;
		 	sheet='Sheet1';
 		run;

Below is test.xlsx

Fig.2

Fig.3

Re: SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 18:27:19 GMT

When I used SAS EG or PC SAS, I never met above issue.

Re: SAS studio import excel file issue

Tom — Thu, 15 Apr 2021 18:30:57 GMT

Do you mean that you ran the second part without ever running the first part? (Or after restarting your SAS session that SAS/studio is using to run your SAS program code).

Or are you saying that if you run that as two separate submissions in the same SAS/Studio session it works differently than when you submit the whole program at once?

If the former then change the setting VALIDVARNAME to what you want in your AUTOEXEC file.

If the later then check what code SAS/Studio is submitting before/after each submission of code in your editor window. It must be resetting VALIDVARNAME in that code.

Re: SAS studio import excel file issue

Reeza — Thu, 15 Apr 2021 18:43:49 GMT

Your first line of code is key:

Option VALIDVARNAME=V7.

Studio and EG have the default set to ANY typically which allows spaces in naming. So if you run the code without that line it may be missing. You also need to verify that the version you're using is displaying either variable names or labels. These are controlled via settings and preferences and could very easily be different in different installations, which is why you may not have encountered this before if it's the first time you're switching between systems.

Re: SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 19:11:08 GMT

Thanks.

It is strange and easy make errors, if the outputs are differences by running step by step or as a whole. I checked the preference in SAS studio page, and did not find somewhere I can change to remove this issue.

What should I do to remove this kind of issue? Should I ask our IT department to reset the environment? Please advise. Thanks

Re: SAS studio import excel file issue

Reeza — Thu, 15 Apr 2021 19:18:39 GMT

To some degree, If you run the exact same code, in the same system you should expect the same results.

From the code shown, you shouldn't see a difference. You didn't show your full code so it's hard to tell where the difference is. If you feel there is a real difference between the two options, run it using one method and then run a proc contents on the file. Then do the same with the other methodology. They should be exactly the same.

I would not expect the same results running on different systems with PROC IMPORT as it's a guessing procedures and by definition it can guess differently. If the file changes monthly you also cannot guarantee that you'll get the same results from month to month, as the data changes there's a small chance that SAS will read in columns with different types and formats.

In general, if you're automating a process it's rare to use PROC IMPORT as it's not consistent, import your data using a data step if you need that type of consistency.

Re: SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 19:28:19 GMT

I submit the code separately.
Yes, you are right. If I submit together VALIDVARNAME keeping equal to V7. However, when I submit separately, VALIDVARNAME is reset as VALIDVARNAME=ANY.

Re: SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 19:36:24 GMT

I run the code in the same environment and same session . The only difference is: one is I submitted separately and another is I submitted the code together. I checked the log by adding "proc options option=validvarname; run;" for the first way I got validvarname=any, and for the second way I got validvarname=v7. That is what I felt some strange.

Re: SAS studio import excel file issue

Tom — Thu, 15 Apr 2021 19:53:38 GMT

To see what type of random code SAS/Studio is inserting you can change a preference setting.

Check the "Show generated code in the SAS log" box.

There is also a setting "SAS variable name policy" that should change the validvarname option.

Re: SAS studio import excel file issue

Reeza — Thu, 15 Apr 2021 19:54:21 GMT

I just checked and SAS Studio appears to run in batch, so when you submit your option it has to be in the same "submit" as your PROC IMPORT otherwise it switches back to ANY as soon as that "submission" is done. That's probably what you're seeing. I have no idea if that's a bug or expected behaviour. It is a change from what happened in EG/Base and not what I would expect to see either.....which is why I tested it 🙂

Under Preferences>Tables you can set the option as V7 or Any via the drop down. If your IT has turned off this setting for you, you may need to ask them to make the change instead. It may impact any other production code you have in place so this isn't' a minor change.

Re: SAS studio import excel file issue

Niugg2010 — Thu, 15 Apr 2021 19:59:39 GMT

Thanks a lot. I changed as you said. It works.