DATA Step, Macro, Functions and more

Unable to import one variable value in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Unable to import one variable value in SAS

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!


Accepted Solutions
Solution
‎08-29-2014 10:21 AM
Super User
Posts: 9,662

Re: Unable to import one variable value in SAS

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

View solution in original post


All Replies
N/A
Posts: 1

Re: Unable to import one variable value in SAS

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.

Occasional Contributor
Posts: 8

Re: Unable to import one variable value in SAS

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?

Super User
Posts: 10,466

Re: Unable to import one variable value in SAS

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.

Solution
‎08-29-2014 10:21 AM
Super User
Posts: 9,662

Re: Unable to import one variable value in SAS

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

Occasional Contributor
Posts: 8

Re: Unable to import one variable value in SAS

Thank you.

Yes, both the codes are working for me. Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 478 views
  • 0 likes
  • 4 in conversation