Excel is reading blank columns as a b c d etc.

Durga_SAS — Wed, 09 Nov 2016 12:58:46 GMT

I am using SAS EG 5.1. when i am importing data from excel, along with data columns it is reading other columns and naming as A, B,C D... ETC.

Is there any option to restrict to read only the valid data colums from SAS?

Thanks,

Durga.

Re: Excel is reading blank columns as a b c d etc.

RW9 — Wed, 09 Nov 2016 14:24:58 GMT

You will find that it is Excel which is telling SAS these columns contain data not SAS. What it is - per most Excel "features" - is things that are hidden behind the scenes, formats on cells, coloring and other formatting can indicate to Excel that there is "contents" in those cells - SAS just checks that information and reads in what Excel tells it contains data. You can see this by highlighting all the columns to the right of your data which you can see don't contain data, highlight them and delete them. The data will then import correctly. It will be something small like formats on a cell or borders or something, Excel considers anything within a cell "data" not just a value.

Just to add an example. If you create a new workbook, and in sheet 1 cell B2 you put:

=concatenate(A1,"")

What have we done here, well A1.value and B1.value both contain nothing. However B1.formula does contain something, so Excel's datarange is set as A1:B1. Now when SAS reads that in, it looks for that data range and reads it in, even though there is no variable. Anything you put into a cell will cause Excel to presume that cell is part of the datarange.

topic Re: Excel is reading blank columns as a b c d etc. in SAS Enterprise Guide

Excel is reading blank columns as a b c d etc.

Re: Excel is reading blank columns as a b c d etc.