DATA Step, Macro, Functions and more

var name starting with number, underscore added vs substituted

Reply
Contributor
Posts: 54

var name starting with number, underscore added vs substituted

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?

 

Super User
Posts: 11,343

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

Posted in reply to geneshackman

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.

PROC Star
Posts: 7,474

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

[ Edited ]
Posted in reply to geneshackman

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

 

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

Contributor
Posts: 54

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

Posted in reply to geneshackman

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

 

Super User
Super User
Posts: 7,050

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

[ Edited ]
Posted in reply to geneshackman

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.

Super User
Posts: 19,814

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

Posted in reply to geneshackman

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

 

Contributor
Posts: 54

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

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

 

Super User
Posts: 19,814

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

Posted in reply to geneshackman

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.

Contributor
Posts: 54

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

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

 

Super User
Posts: 19,814

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

Posted in reply to geneshackman

And your colleagues information?

Contributor
Posts: 54

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

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

Super User
Posts: 19,814

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

Posted in reply to geneshackman

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. 

 

 

Contributor
Posts: 54

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

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?

 

Super User
Posts: 19,814

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

Posted in reply to geneshackman

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. 

Contributor
Posts: 54

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

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.

Ask a Question
Discussion stats
  • 19 replies
  • 735 views
  • 0 likes
  • 6 in conversation