topic Re: Proc import stops reading file before last record in SAS Programming

Proc import stops reading file before last record

metro — Wed, 20 Dec 2017 19:56:27 GMT

My xlsx file has 4650 records and 7 columns. Proc import only reads in 4500 records. When I run the file through stat transfer and create a new file the new file will import correctly. SAS version 9.3.

Re: Proc import stops reading file before last record

Tom — Wed, 20 Dec 2017 20:05:18 GMT

Works fine for me.

1294  libname x xlsx "&path\test file.xlsx";
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Downloads\test file.xlsx
1295  proc copy inlib=x outlib=work;
1296  run;

NOTE: Copying X.TEST to WORK.TEST (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 4560 observations and 7 variables.
NOTE: There were 4560 observations read from the data set X.TEST.
NOTE: The data set WORK.TEST has 4560 observations and 7 variables.

How did you try to read it?

Re: Proc import stops reading file before last record

metro — Wed, 20 Dec 2017 20:53:56 GMT

I used proc import. I renamed the sheet to test before I uploaded and now I get all 4560 records. However, the one I was originally using had a sheet name with >21 characters and that one still stops importing at 4500 records. Looks like the length of the sheetname is the problem.

Re: Proc import stops reading file before last record

Tom — Wed, 20 Dec 2017 21:07:04 GMT

I wouldn't bother with using PROC IMPORT if you can get the LIBNAME engine to work.
21 character member name shouldn't be a problem since SAS names can be up to 32 characters long, unless you have option VALIDVARNAME=V6 set.
You might have made other changes to the file when you opened it to change the sheetname.