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

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

GeorgeSAS — Mon, 31 Oct 2016 15:34:20 GMT

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!

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

GreggB — Mon, 31 Oct 2016 15:43:47 GMT

Try using the range option:

 RANGE='A13:D113';

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

GeorgeSAS — Mon, 31 Oct 2016 16:08:52 GMT

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

Thanks

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

GreggB — Mon, 31 Oct 2016 16:12:13 GMT

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

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

ballardw — Mon, 31 Oct 2016 16:56:27 GMT

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.

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

Kurt_Bremser — Mon, 31 Oct 2016 18:05:48 GMT

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.

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

KRusso — Tue, 01 Nov 2016 01:50:25 GMT

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;

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

GeorgeSAS — Wed, 02 Nov 2016 12:37:09 GMT

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.

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

KRusso — Wed, 02 Nov 2016 13:31:58 GMT

I am current using libname statement in 9.4. libname mylib pcfiles path="&BalFldr&ReturnChecks.";

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

GeorgeSAS — Wed, 02 Nov 2016 18:23:23 GMT

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!

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

Tom — Wed, 02 Nov 2016 18:30:06 GMT

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

libname mylib xlsx 'myfile.xlsx';