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.
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.
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!
EJ
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 25. 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.