Unable to import one variable value in SAS

[Mrinal]

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!

[karabas]

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.

[Mrinal]

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?

[ballardw]

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.

[Ksharp · Accepted solution]

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

[Mrinal]

Thank you.

Yes, both the codes are working for me.