topic Re: error in importing an xls file through data step in SAS Programming

error in importing an xls file through data step

Mrinal — Thu, 28 Aug 2014 14:20:07 GMT

Hi All,

It would be of great help if you can help me with this, please.

I am trying to export an xls file using SAS using the the below code, but am getting the following error. The file has 13 variables and 78 observations.

The code is as follows:

libname SAS "C:\Users\mrinal\Desktop\test";

filename test "C:\Users\mrinal\Desktop\test\Badahar.xls";

data SAS.z1;

infile test truncover dsd firstobs=2;

informat Amount_outstanding 5.2;

informat Collateral_category $18.;

informat Comments $15.;

informat F13 $6.;

informat Formal_informal $8.;

informat Household_number best8.;

informat Interest_category best8.;

informat Interest_outstanding 6.2;

informat Loan_number best8.;

informat Principal_outstanding best8.;

informat Type_of_loan $11.;

informat UPDATETIME $255.;

informat Village $6.;

format Amount_outstanding 5.2;

format Collateral_category $18.;

format Comments $15.;

format F13 $6.;

format Formal_informal $8.;

format Household_number best8.;

format Interest_category best8.;

format Interest_outstanding 6.2;

format Loan_number best8.;

format Principal_outstanding best8.;

format Type_of_loan $11.;

format UPDATETIME $255.;

format Village $6.;

input

Amount_outstanding

Collateral_category $

Comments $

F13 $

Formal_informal $

Household_number

Interest_category

Interest_outstanding

Loan_number

Principal_outstanding

Type_of_loan $

UPDATETIME $

Village $

;run;

proc print data=SAS.z1;run;

Error I am getting is:

NOTE: The infile TEST is:

Filename=C:\Users\mrinal\Desktop\test\Badahar.xls,

RECFM=V,LRECL=256,File Size (bytes)=30720,

Last Modified=28Aug2008:10:24:49,

Create Time=27Aug2008:08:09:59

NOTE: Invalid data for Amount_outstanding in line 1 1-6.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--

1 CHAR ÐÏ.à¡± 6

ZONE DC1EAB

NUMR 0F1011

Amount_outstanding=. Collateral_category= Comments= F13= Formal_informal= Household_number=.

Interest_category=. Interest_outstanding=. Loan_number=. Principal_outstanding=. Type_of_loan=

UPDATETIME= Village= _ERROR_=1 _N_=1

NOTE: 1 record was read from the infile TEST.

The minimum record length was 6.

The maximum record length was 6.

NOTE: The data set SAS.Z1 has 1 observations and 13 variables

Thanks in advance for your support.

Re: error in importing an xls file through data step

RW9 — Thu, 28 Aug 2014 14:29:22 GMT

You cannot import XLS files like that. The XLS file format is a proprietary binary MS format, not a text file that can just be read. Use the import procedure, or the libname xls to read in the data, or alternatively save the Excel data as CSV which you could then read in via your datastep.

Re: error in importing an xls file through data step

esjackso — Thu, 28 Aug 2014 14:35:21 GMT

I might be confusing things but the above code is set to import data from a excel sheet and not export data as you described in your post.

Im pretty sure that the dsd option on infile defaults to a comma delimiter instead of a tab delimiter. But again that would be for importing the data from excel.

If you have SAS/ACcess for pc files you can do either an import or and export using libname syntax:

Import:

libname test excel "C:\Users\mrinal\Desktop\test\Badahar.xls"; *once you run this you should see it listed in the sas explorer on the left;

data yourdata; *creates the sas dataset;

set test.'yoursheetname$'n;

run;

libname test clear; *clears your connection to the excel file;

Export -- basically the same but the data step is reversed.

Data test.'sheetname'n;

set yourdata;

run;

See if any of this helps!