Question: in our raw data set, some variables begin with the year, for example, "2007SomeOtherWords"
On one person's computer, after SAS reads this in, the variable name looks like "_007SomeOtherWords"
On another person's computer, after SAS reads this in, the variable name looks like "_2007SomeOtherWords"
On one computer, the underscore substitutes for the first character, on another computer, the underscore is added before the first character. What determines whether the underscore is added or substituted?
As far as I can tell, our computers are identical, SAS 9.4, 64 bit SAS, windows 7, same service pack, 64 bit (also we are reading from an excel file, excel is 32 bit, MS 2016, but the same on both computers). Also, he can read the excel file with dbms=excel, I can't. I have to use dbms=xlsx
Any suggestions?
If the first character is a space in the file read using Proc Import you could get the _2007
Other conversions of names might occur depending on the data source such are reading from another DBMS. You would have to provide some details as to how the data source is being read to provide more information.
Also the VALIDVARNAME system option in SAS could come into play or in Connect to a DBMS.
I think the difference is between the different DBMS you are using. The following is from the 9.4 SAS/Access documentation (http://support.sas.com/documentation/cdl/en/acpcref/69731/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...😞
2013.Changes
becomes _2013_Changes
.Default | YES |
Restrictions | Valid only for Windows. |
Valid only for the IMPORT procedure. | |
Supported only when DBMS=EXCEL5. | |
VALIDVARNAME= must be V7. It cannot be ANY. |
HTH,
Art, CEO, AnalystFinder.com
Thanks for the quick responses. A little more info.
On his computer, this works
PROC IMPORT OUT = YRBS
DATAFILE = "H:\PHIGDATA\PreventionAgenda\Data\2017\RawData\YRBS\PA52_PA56_PA57_07_15_raw_data.xlsx"
DBMS=EXCEL REPLACE;
RUN;
On my computer, I get
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
but on my computer, this works
PROC IMPORT OUT = YRBS
DATAFILE = "H:\PHIGDATA\PreventionAgenda\Data\2017\RawData\YRBS\PA52_PA56_PA57_07_15_raw_data.xlsx"
DBMS=xlsx REPLACE;
RUN;
but I get this
NOTE: Variable Name Change. 2007 -> _2007__
NOTE: Variable Name Change. 2007 Lower CI Limit -> _2007_Lower_CI_Limit
NOTE: Variable Name Change. 2007 Upper CI Limit -> _2007_Upper_CI_Limit
NOTE: Variable Name Change. 2009 -> _2009__
NOTE: Variable Name Change. 2009 Lower CI Limit -> _2009_Lower_CI_Limit
NOTE: Variable Name Change. 2009 Upper CI Limit -> _2009_Upper_CI_Limit
On his computer, the variable names look like _007 and _007_Lower_CI_Limit
You should either both use the EXCEL engine or the XLSX engine if you want to get the same behaviour.
The XLSX engine is more portable since it does not require that you have Microsoft software running somewhere.
But the XLSX engine has fewer options and does require a newer version of SAS.
If EXCEL engine doesn't work on one of the machines then that machine does not have Microsoft Excel installed or the version that is installed is not compatible with the version of SAS installed. If you have 64bit SAS and 32bit Excel or vice versa you will have trouble.
Also if you are using EXCEL enging then the version of Excel that is install could make a difference in how the import works.
Can you post the results from proc setinit on each computer - block out the Site number.
Which part are you looking for? This is mine, with site info and dates edited out
Original site validation data
Site name: 'stuff'.
Site number: numbers.
Expiration: date.
Grace Period: 45 days (ending date).
Warning Period: 45 days (ending date).
System birthday: date.
Operating System: WX64_WKS.
Product expiration dates:
---Base SAS Software
date
---SAS/STAT
date
---SAS/GRAPH
date
---SAS/ETS
date
---SAS/FSP
date
---SAS/OR
date
---SAS/ASSIST
date
---SAS/CONNECT
date
---SAS/Secure 168-bit
date
---SAS/Secure Windows
date
---SAS Enterprise Guide
date
---SAS Bridge for ESRI
date
---OR OPT
date
---OR PRS
date
---OR IVS
date
---OR LSO
date
---SAS/ACCESS Interface to Oracle
date
---SAS/ACCESS Interface to Sybase
date
---SAS/ACCESS Interface to PC Files
date
---SAS/ACCESS Interface to ODBC
date
---SAS Workspace Server for Local Access
date
---High Performance Suite
date
I wanted to see the Current Version info, but it looks like you deleted it? It's the first line under Original Site Validation data.
Hmm, I don't think I deleted any lines, just some content (dates, site name, site number). I reran proc setinit, and these are the first couple of lines. I don't see a line for current version info. Is there another way to get this?
Original site validation data
Site name: 'site name'.
Site number: number.
Expiration: date.
Grace Period: 45 days (ending date).
Warning Period: 45 days (ending date).
System birthday: date.
Operating System: WX64_WKS.
If I go to sas help, I got this
software information
sas 9.4 TS level 1MO
x64_7PRO platform
Operating System Information
Windows version 6.1.7601
And your colleagues information?
Reeza, I went to look at his computer, exactly the same info as my computer. Can't figure this out.....
I guess, my response would be, that can't be the case.
There has to be some difference, which is why I asked you to post the exact information.
Especially if you say you can't use an DBMS engine and s/he can. Please post the exact values. Otherwise, your best bet is to work through this with SAS Support. I wouldn't expect the exact same versions on different computers to handle things differently.
Reeza, I agree, there has to be some difference, but I can't find it. If I were to post the info about his computer that I posted about my computer, it would be exactly the same.
Is there something in an .ini file or something that tells SAS your preferences, maybe for some reason his is config'd differently than mine, where would I find how our SAS's are config'd? or the preferences?
PROC OPTIONS.
Assuming you have the exact same version, 9.4 TS1M0 - which is buggy in the first place actually, the other option I'd check would be VALIDVARNAME but I doubt you'll find a difference there. The inability to access Excel file the same means that something is not set up correctly on your machine.
For sure somehow the computers are set up differently. However, I just tried the two versions on two other computers in our office, and get exactly the same problem as I do when I try on my computer. So, somehow my one colleague's computer is set up differently than is all the others, but I can't figure out how. Thanks though.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.