DATA Step, Macro, Functions and more

specifying variable names row using libname xlsx

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

specifying variable names row using libname xlsx

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?


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 23,306

Re: specifying variable names row using libname xlsx

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;

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,482

Re: specifying variable names row using libname xlsx

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

PG
Contributor
Posts: 23

Re: specifying variable names row using libname xlsx

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

Solution
3 weeks ago
Super User
Posts: 23,306

Re: specifying variable names row using libname xlsx

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;
Super User
Posts: 3,860

Re: specifying variable names row using libname xlsx

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 108 views
  • 3 likes
  • 4 in conversation