DATA Step, Macro, Functions and more

SAS import excel problem(got 255 columns,usless empty columns read)

Reply
Regular Contributor
Posts: 208

SAS import excel problem(got 255 columns,usless empty columns read)

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!

Super Contributor
Posts: 268

Re: SAS import excel problem(got 255 columns,usless empty columns read)

Try using the range option:

 

 RANGE='A13:D113';
Regular Contributor
Posts: 208

Re: SAS import excel problem(got 255 columns,usless empty columns read)

the range works but is it necessary? the excel only has 25 columns

Thanks
Super Contributor
Posts: 268

Re: SAS import excel problem(got 255 columns,usless empty columns read)

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

Super User
Posts: 10,516

Re: SAS import excel problem(got 255 columns,usless empty columns read)

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.

Super User
Posts: 6,946

Re: SAS import excel problem(got 255 columns,usless empty columns read)

There's a function in Excel that moves the cursor to the "last" cell, which will show you what Excel thinks is the maximum used column and row.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: SAS import excel problem(got 255 columns,usless empty columns read)

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;

Regular Contributor
Posts: 208

Re: SAS import excel problem(got 255 columns,usless empty columns read)

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.

 

 

Occasional Contributor
Posts: 10

Re: SAS import excel problem(got 255 columns,usless empty columns read)

I am current using libname statement in 9.4. libname mylib pcfiles path="&BalFldr&ReturnChecks.";
SAS_version.PNG
Regular Contributor
Posts: 208

Re: SAS import excel problem(got 255 columns,usless empty columns read)

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!

 

Super User
Super User
Posts: 6,502

Re: SAS import excel problem(got 255 columns,usless empty columns read)

[ Edited ]

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

 

libname mylib xlsx 'myfile.xlsx';

 

Ask a Question
Discussion stats
  • 10 replies
  • 377 views
  • 0 likes
  • 6 in conversation