topic Re: Import .XLS extra empty column in SAS Programming

Import .XLS extra empty column

Roger12 — Tue, 14 Jun 2016 04:11:10 GMT

Dear all,

When I try to import the attached xls file, I get the empty O column....how knows why? how I get rid of it?

Many thanks

Roger

Re: Import .XLS extra empty column

ChrisNZ — Tue, 14 Jun 2016 04:36:34 GMT

Something must have been typed in it at some point.

Just drop it:

proc import out=TABLE(drop=O) ...

Re: Import .XLS extra empty column

Ksharp — Tue, 14 Jun 2016 05:40:01 GMT

proc import datafile='/folders/myfolders/health_exam_results.xls' 
out=have 
replace 
dbms=xls;
range='health_exam_results$A1:N100';
run;

Re: Import .XLS extra empty column

ballardw — Tue, 14 Jun 2016 14:26:55 GMT

The "WHY" in a bit more detail than @ChrisNZ mentions is that if a column or row in Excel has a value entered in a cell and then the value is deleted Excel "remembers" the column or row has been used. So when you ask Excel to tell you what is in the data via Proc Import it tells the data linkage that column has data.

You might also want to check for entire rows at the end of your data set without values.

Re: Import .XLS extra empty column

samgautam007 — Thu, 01 Feb 2018 07:10:37 GMT

@ballardw that was a very good explanation. I just created new excel sheet and no issues so far. Even i was having blank columns while importing because i highlight whole row.

After creating new excel , everything is okay.

many thanks for explaining WHY?

regards

Sandeep