DATA Step, Macro, Functions and more

SAS Import xlsx file

Reply
Contributor scb
Contributor
Posts: 69

SAS Import xlsx file

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;

Trusted Advisor
Posts: 1,137

Re: SAS Import xlsx file

Please try mixed = yes option

PROC IMPORT DATAFILE="D:\Book1.xlsx"
dbms=excel replace out=mydata;
GETNAMES=yes;
MIXED=yes;
RUN;
Thanks,
Jag
Contributor scb
Contributor
Posts: 69

Re: SAS Import xlsx file

Posted in reply to Jagadishkatam

I have tried but still got the same answer.

Super User
Posts: 7,868

Re: SAS Import xlsx file

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,046

Re: SAS Import xlsx file


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;

Ask a Question
Discussion stats
  • 4 replies
  • 343 views
  • 1 like
  • 4 in conversation