topic SAS Import xlsx file in SAS Programming

SAS Import xlsx file

scb — Wed, 30 Nov 2016 01:15:06 GMT

I tried to import the attached xlsx file; in the HOLNO column, most of the data were imported except the last row which is in character format.

I would like to import the HOLNO column as character format wihout modifying the excel file.

Anyone can help? Thanks.

My code:

PROC IMPORT DATAFILE="D:\Book1.xlsx"
dbms=excel replace out=mydata;
GETNAMES=yes;
RUN;

Re: SAS Import xlsx file

Jagadishkatam — Wed, 30 Nov 2016 03:18:12 GMT

Please try mixed = yes option

PROC IMPORT DATAFILE="D:\Book1.xlsx"
dbms=excel replace out=mydata;
GETNAMES=yes;
MIXED=yes;
RUN;

Re: SAS Import xlsx file

scb — Wed, 30 Nov 2016 06:38:25 GMT

I have tried but still got the same answer.

Re: SAS Import xlsx file

Kurt_Bremser — Wed, 30 Nov 2016 08:13:57 GMT

Export to a csv file from Excel and write a data step to read that; that way you have full control over the variables and are not subject to the idiosyncrasies of the Excel file format and the guessing of proc import.

Re: SAS Import xlsx file

Ksharp — Wed, 30 Nov 2016 09:46:43 GMT


Add MIXED='yes' ;  option

Or not sure if the following could work.

libname xx excel "/folders/myfolders/Book1.xlsx";
data have;
set xx.'Sheet1'n(dbsastype=(holno='char(100)'));
RUN;