Hi All,
I would request your help with the following SAS query, I am able to import all variable values from the .xls file except one (Interest_outstanding) using proc import statement.
My code is..
libname SAS "C:\Users\mrinal\Desktop\test";
proc import datafile = "C:\Users\mrinal\Desktop\test\Badahar.xls" out=SAS.g1 ;run;
Request your help on this. Attached is the .xls file.
Thanks in advance!
This worked for me .
proc import datafile = "C:\temp\Badahar.xls" dbms=xls replace out=x ;
run;
proc import datafile = "C:\temp\Badahar.xls" dbms=excel replace out=x ;
mixed=yes;
run;
Xia Keshan
SAS analyzed first 5 rows to determine variable format. Your 1st 5 values are blank and SAS assign to this variable Character.
Enter 0 (zero) for Interest_outstanding in first row and save file. Now you will be able to import it correctly.
Well, if i assume that the following things imply:
blank= data missing for the variable
0= zero value for that particular observation and
x = any other number.
So putting a zero in the first row would imply that the value will be taken as zero where as actually, the data might be missing. Any other way to go about this?
Look in the online help for Microsoft Excel Workbook Files on how to set the WINDOWS registry to look at more rows before assigning a column type. The type is basically controlled by the MS application, NOT SAS.
Or export the data to CSV and use a larger value for guessing rows on import.
The whole direct Excel to SAS import using proc import is fairly limited for your control so there are oddities that can occur.
This worked for me .
proc import datafile = "C:\temp\Badahar.xls" dbms=xls replace out=x ;
run;
proc import datafile = "C:\temp\Badahar.xls" dbms=excel replace out=x ;
mixed=yes;
run;
Xia Keshan
Thank you.
Yes, both the codes are working for me.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: