topic Re: specifying variable names row using libname xlsx in SAS Programming

specifying variable names row using libname xlsx

Batman — Thu, 31 May 2018 19:31:50 GMT

If I'm reading in an Excel worksheet using the libname xlsx option and the variable names are on 3rd row, for example, is there a way to tell SAS to use that row rather than the first row?

Re: specifying variable names row using libname xlsx

PGStats — Thu, 31 May 2018 19:55:21 GMT

You can specify your data table position with the syntax myLib.'mySheet$A3:G200'n

Re: specifying variable names row using libname xlsx

Batman — Thu, 31 May 2018 20:20:04 GMT

Thanks, what if the combine file name and range exceed 32 characters?

26 GOPTIONS ACCESSIBLE;

27 data temp;

28 set

29 test.'ROA13 TABLE 1 - DATA SET WORK$A3:G200'n;

_____________________________________________

307

ERROR 307-185: The data set name cannot have more than 32 characters.

30 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TEMP may be incomplete. When this step was stopped there were 0 observations and 6 variables.

WARNING: Data set WORK.TEMP was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: specifying variable names row using libname xlsx

Reeza — Thu, 31 May 2018 20:38:10 GMT

Instead of LIBNAME you can try PROC IMPORT with the DBMS = XLSX engine.

You can specify the RANGE there, or at least the starting. If you include 0 as the end, it reads all the data.

Untested code and not sure if it work:

proc import out=want datafile='path to xlsx' dbms=xlsx; 
range='ROA13 Table 1 - Data Set Work$A3:0';
run;

Re: specifying variable names row using libname xlsx

SASKiwi — Fri, 01 Jun 2018 02:44:28 GMT

The 32 character limitation is being fixed in SAS 9.5, due out towards the end of the year you will then be able to use up to 255 characters.