topic Proc Import XLSX data repeating for null values in SAS Enterprise Guide

Proc Import XLSX data repeating for null values

darrenhoggard — Tue, 23 Oct 2018 14:39:59 GMT

I am importing an XLSX file using the proc import code:

PROC IMPORT OUT= &outDS.
DATAFILE= "&inFile."
DBMS=xlsx REPLACE;
range="&sheetNM.$A1:AZ";
GETNAMES=YES;
RUN;

However, some of the date columns have no data in them, but when SAS is importing it, it is populating the field with the value from the previous line:

REF1	Start Date	End Date
ABC123	15-May-15	17-Aug-18
ABC234	11-Jun-18	17-Aug-18
ABC345	14-Dec-15	17-Aug-18
ABC456	17-Feb-17	17-Aug-18
ABC567	06-Mar-15	17-Aug-18
ABC898	10-Mar-15	17-Aug-18

Only the first row should have the end date as 17/08/2018, all the others are blank in the original date.

How is this fixed.

Re: Proc Import XLSX data repeating for null values

Reeza — Tue, 23 Oct 2018 14:43:36 GMT

Is the cell merged? Can you attach a sample of the Excel file? I've never seen this occur so very curious under what circumstances it would occur.

Re: Proc Import XLSX data repeating for null values

darrenhoggard — Tue, 23 Oct 2018 14:50:16 GMT

hi,

I think it is something in excel.

the file is received from a third party, when I amended some of the data and saved as a new workbook, it was imported into SAS ok.

I will have to go back to the third party and see what they are doing!

thnaks

Re: Proc Import XLSX data repeating for null values

Kurt_Bremser — Tue, 23 Oct 2018 14:59:58 GMT

Bottom line: it is foolish to expect consistent, usable results from Excel files.

Save the data to a csv file, which you can inspect with a text editor, and read it with a data step written along the documentation you get with the file.

(no documentation -> return to sender)