topic Re: Read in all records from Excel file in SAS Programming

Read in all records from Excel file

Walternate — Fri, 14 Oct 2022 16:52:29 GMT

Hi,

I am using a very basic libname xlsx step to read in a set of Excel files:

%let datafile = mypath\myfile.myext

libname xl xlsx "&Datafile.";

%let keeplist = v1 v2 'Variable 3'n 'Variable 4'n;

data mydata;
length v1 $20. v2 $500. 'Variable 3'n 'Variable 4'n $1000. ;

set xl.mysheet (keep=&keeplist.);

run;

libname xl clear;

This reads each file in successfully with no errors in the log. However, I noticed that rows from some Excel file are not being read in. I don't notice any level of missingness on the first row not being read in, so I'm not sure what's going on here.

Any help is much appreciated.

Re: Read in all records from Excel file

ballardw — Fri, 14 Oct 2022 17:39:56 GMT

You should provide an example of file that demonstrates that behavior.

Tell us which row(s) are not read.

I realize that your source files may be sensitive. I am not asking for a file containing sensitive information but a file that shows the behavior. That may mean making a copy and replacing sensitive fields with values like XXXXX or 1111 (if the column should be numeric).

Re: Read in all records from Excel file

Reeza — Fri, 14 Oct 2022 17:46:57 GMT

Check the excel files for named ranges that are the same as the sheetname but not the full selection of data.