BookmarkSubscribeRSS Feed
GeorgeSAS
Lapis Lazuli | Level 10

Hello,

 

When I use proc import to read excel .xlsx file.

there are total 25 column in the excel.but after I read it into SAS, it cost me longer time and read a 255 column SAS data set ,

230 columns are empty. I don't know what problem is this. this happend on some special excel file while other excel files don't have problem.

I can't see any different among these excel files.

 

Thanks!

10 REPLIES 10
GreggB
Pyrite | Level 9

Try using the range option:

 

 RANGE='A13:D113';
GeorgeSAS
Lapis Lazuli | Level 10
the range works but is it necessary? the excel only has 25 columns

Thanks
GreggB
Pyrite | Level 9

Adjust the range to fit what you want. For 25 columns, it would be A <whatever>: Y<whatever

ballardw
Super User

Note that Excel will occasionally treat a column as having "something" even after deleting the data in the columns.

You didn't say how you read the data into SAS but many of the approaches rely on Excel telling SAS about the contents. When Excel has "ghost" columns then they get imported.

 

One thing you might try is exporting the file to CSV and see if you get hundreds of commas as the end of each line when you look at the exported file with a text editor or something like Wordpad. If so, then you have ghost columns.

 

Excel will also do this with rows.

KRusso
Obsidian | Level 7

Have you tried using the libname approach? I created a bunch of columns, then deleted the data from them to attempt to simulate your issue. I saved the file as test.xlsx. make sure you clear the libname connection or you cannot access the excel file. more info is provided in the base Sas 1 course.

 

libname xl pcfiles path="&Mypath\test.xlsx";

data STuffInTest;

set xl.'Sheet1$'n; /* must use literal for sheet name */

run;

libname xl clear;

GeorgeSAS
Lapis Lazuli | Level 10

The libname method is very good but I have SAS 9.4(even 9.3)) which not support this method.(I knew only 9.2 support this)

 

 

 

Thanks.

 

 

KRusso
Obsidian | Level 7
I am current using libname statement in 9.4. libname mylib pcfiles path="&BalFldr&ReturnChecks.";
SAS_version.PNG
GeorgeSAS
Lapis Lazuli | Level 10

I tried with your mothod, it works! you add pcfiles engine so that works!

 

while my old method not work  at 9.4 but works at 9.2:

 

LIBNAME Myfile1 "C:temp\test.xlsx";

 

Thank you very much!

 

Tom
Super User Tom
Super User

SAS 9.4 can use engines to read/write XLSX and XLS file directly on both Unix and Windows.

 

libname mylib xlsx 'myfile.xlsx';

 

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
  • 10 replies
  • 4159 views
  • 0 likes
  • 6 in conversation