Column names in SAS studio

[greveam]

My company just migrated from desktop SAS (base SAS 9.4) to SAS studio. Today, I received some xlsx files and tried to open these files in SAS studio using this command:

PROC IMPORT DATAFILE="/sasfolders/user/anders/kb - data.xlsx"
OUT=test
DBMS=XLSX
REPLACE;
RUN;

Some of the variables in the excel file had names with a delimiter, e.g. old age, which SAS previously (desktop version) would rename to a valid SAS name like old_age. In the SAS studio version though, using:

PROC contents DATA=test; RUN;

The variable name in SAS studio seems to be the same as in excel using the name with a delimiter (old age), which I assume is still an invalid SAS name? So in the background SAS must have renamed the variable even though I can't see it in the SAS studio interface.

This is further complicated by the fact that I can't even run a proc freq because I can't figure out what SAS studio has assigned as a valid SAS name to the various columns.

Any help would be much appreciated. Thanks!!! Anders

[ballardw]

The later versions of SAS allow a couple of options to use non-SAS variable or table names because of connection features to other file types. I suspect the system option Validvarname=Any is the default for some setups or situations.

I would try adding

options validvarname=V7;

before your Proc import code.

That should get the previous behavior of replacing characters with _ that aren't valid for typical SAS names.

If you get a name from a process like this that has non-standard names you would use "funny name"n to reference it. The quotes with the n immediately following the closing quote denote a name literal. 

I would really suggest trying the validvarname setting prior to import as the headaches involved with remembering to put lots of names with the "text"n gets very annoying quickly.

[Reeza]

Just to add on, I'd add this option to your autoexec file so that it's the default from now own. I know in SAS UE you can get to the autoexec from the menu in SAS Studio.

[greveam]

Thanks! the v7 option solves the problem with the variables having non-valid SAS names. The next problem is that SAS studio has automatically converted the row types from numbers (assigned in excel) to characters. I could format the row type back to numbers in SAS, but is it really true that moving to SAS studio adds all these extra steps that were not required in base SAS?

For the record, I tried importing the same excel file on my other laptop with base SAS (using the excel important function) and experienced none of the problems with non-valid SAS names or that SAS changes numbers to character rows.

[Tom]

---

@greveam wrote:

Thanks! the v7 option solves the problem with the variables having non-valid SAS names. The next problem is that SAS studio has automatically converted the row types from numbers (assigned in excel) to characters. I could format the row type back to numbers in SAS, but is it really true that moving to SAS studio adds all these extra steps that were not required in base SAS?

For the record, I tried importing the same excel file on my other laptop with base SAS (using the excel important function) and experienced none of the problems with non-valid SAS names or that SAS changes numbers to character rows.

An Excel file will allow you to put any type of data into any cell.  That is the nature of a spreadsheet.  A SAS dataset (or any database/analysis system) has a fixed type for a variable across all observations.

If PROC IMPORT is reading a variable as character then at least one of the cells in that column does not contain a number.  If you want the variable to be a numeric in SAS then make sure that NONE of the cells in that column are character strings instead of numbers.

For more detailed help create a SMALL example file that you are finding that SAS does not import as you expect and post that and perhaps someone can do a more detailed analysis.

[greveam]

Thanks Tom! but I don't think you read my post. Importing the same xls file in base SAS vs. SAS studio results in SAS assigning exactly the same columns as numeric and character, respectively. Anyone else had this experience after migrating to SAS studio?

[Tom]

---

@greveam wrote:

Thanks Tom! but I don't think you read my post. Importing the same xls file in base SAS vs. SAS studio results in SAS assigning exactly the same columns as numeric and character, respectively. Anyone else had this experience after migrating to SAS studio?

---

Are you really reading XLS files instead of XLSX files?

You will need to post the SAS log(s) of the code used to import the tables on both system to see how the code is different.  SAS has many import engines for Excel files.  I find it is best to use the XLSX engine as that does not vary based on what Microsoft software you have installed on your machine, plus it works on non-windows SAS servers.  It is still worth the effort for you to reduce one of your problem files to a minimal set of rows and columns that can demonstrate the issue.  You will probably find the cause on your own just by doing that exercise.

Also if you are using Enterprise Guide to import the file then that follows a totally different process. It will read your file and convert it to a text file that it uploads to SAS and reads with a data step that it generates.

[Reeza]

I'm also guessing that you've upgraded a version as well, not doing a straight move from SAS 9.4 TS1M6 on Base to SAS 9.4 TS1M6 on Studio. Can you verify your versions in each application?

You can check what you have licensed in SAS using:

proc setinit; run;

And what's installed on your system using:

proc product_status; run;

The output will be in the log. 

If that is the case, then yes, the Excel engine is undergoing continuous upgrades so you cannot expect exactly the same behaviour from a GUESSING procedure. Excel doesn't enforce types so SAS has to guess what fields are which and since it's upgrading that algorithm with each version it's possible to get changes. If you want to enforce types, use a different data type that will allow you to specify types. 

[greveam]

I believe I have found the the culprit;

when running the proc setinint; run; and proc product_status; run;

the log is comparable between base SAS and SAS studio (both 9.4_M5), aside from the add-ins meant to "enhance" data sharing between SAS studio and microsoft products:

 ---SAS Add-in for Microsoft Excel

 ---SAS Add-in for Microsoft Outlook

 ---SAS Add-in for Microsoft PowerPoint

 ---SAS Add-in for Microsoft Word                                                                       

I will bring this up with the IT department tomorrow. Thanks a million.