BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Batman
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
PGStats
Opal | Level 21

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

PG
Batman
Quartz | Level 8

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

Reeza
Super User

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;
SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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