topic Re: var name starting with number, underscore added vs substituted in SAS Programming

var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 18:43:47 GMT

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?

Re: var name starting with number, underscore added vs substituted

ballardw — Fri, 17 Feb 2017 18:53:11 GMT

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.

Re: var name starting with number, underscore added vs substituted

art297 — Fri, 17 Feb 2017 18:59:08 GMT

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#n0msy4hy1so0ren1acm90iijxn8j.htm😞

Starting in SAS/ACCESS 9.4, GETNAMES= prefixes an underscore to the data value rather than replacing the value’s first character. For example, 2013.Changes becomes _2013_Changes.

YES specifies that the IMPORT procedure generate SAS variable names from the data values in the first record of the imported Excel file.

NO specifies that the IMPORT procedure generate SAS variable names as F1, F2, F3, and so on.

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

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 19:11:05 GMT

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

Re: var name starting with number, underscore added vs substituted

Reeza — Fri, 17 Feb 2017 19:34:36 GMT

Can you post the results from proc setinit on each computer - block out the Site number.

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 19:50:04 GMT

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

Re: var name starting with number, underscore added vs substituted

Tom — Fri, 17 Feb 2017 20:47:14 GMT

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.

Re: var name starting with number, underscore added vs substituted

Reeza — Fri, 17 Feb 2017 20:19:28 GMT

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.

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 20:32:56 GMT

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

Re: var name starting with number, underscore added vs substituted

Shmuel — Fri, 17 Feb 2017 20:33:31 GMT

If the excel files are not identical on both computers, I guess

that the longest text under "_2007..." is at least 20 characters while under "_007..." is at most 18 characters.

Check the excel data - am I right ?!

As about beeing unable to read dbms=excel - I have no idea - as both computers are with SAS 9.4

Re: var name starting with number, underscore added vs substituted

Reeza — Fri, 17 Feb 2017 20:34:28 GMT

And your colleagues information?

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 20:35:28 GMT

Shmuel, we are both accessing the same excel file, on a shared drive.

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 20:40:53 GMT

Reeza, I went to look at his computer, exactly the same info as my computer. Can't figure this out.....

Re: var name starting with number, underscore added vs substituted

Reeza — Fri, 17 Feb 2017 20:43:28 GMT

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.

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 20:48:05 GMT

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?

Re: var name starting with number, underscore added vs substituted

Reeza — Fri, 17 Feb 2017 20:57:34 GMT

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.

Re: var name starting with number, underscore added vs substituted

geneshackman — Fri, 17 Feb 2017 21:23:00 GMT

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.

Re: var name starting with number, underscore added vs substituted

art297 — Fri, 17 Feb 2017 21:58:05 GMT

I would contact SAS Technical Support. Here is a message contained in a SAS note (http://support.sas.com/kb/43/933.html) regarding a closely related problem:

This problem (getting the error you got when trying to import using the Excel engine) can also occur when the Microsoft components that the drivers require are not installed. If this is true, contact SAS Technical Support.

HTH,

Art, CEO, AnalystFinder.com

Re: var name starting with number, underscore added vs substituted

ballardw — Fri, 17 Feb 2017 23:55:10 GMT

Assuming you are using Proc Import then the Version of EXCEL may something to consider. The Excel data engine decides what SAS sees and then SAS uses that information to create the variable names.

Re: var name starting with number, underscore added vs substituted

geneshackman — Tue, 21 Feb 2017 16:22:49 GMT

Thanks everyone for trying to figure this out. I am going a different way, as I can't quite figure it out. Instead, I will use validvarname=any to take the variables exactly as they are named, and then just use rename to get them to a 'valid' sas name. Thanks all.