topic Re: Unable to import one variable value in SAS in SAS Programming

Unable to import one variable value in SAS

Mrinal — Thu, 28 Aug 2014 15:22:15 GMT

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!

Re: Unable to import one variable value in SAS

karabas — Thu, 28 Aug 2014 15:42:58 GMT

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.

Re: Unable to import one variable value in SAS

Mrinal — Thu, 28 Aug 2014 15:52:50 GMT

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?

Re: Unable to import one variable value in SAS

ballardw — Thu, 28 Aug 2014 18:39:01 GMT

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.

Re: Unable to import one variable value in SAS

Ksharp — Fri, 29 Aug 2014 14:21:24 GMT

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

Re: Unable to import one variable value in SAS

Mrinal — Fri, 29 Aug 2014 14:35:44 GMT

Thank you.

Yes, both the codes are working for me.