08-28-2014 10:20 AM
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";
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.;
proc print data=SAS.z1;run;
Error I am getting is:
NOTE: The infile TEST is:
RECFM=V,LRECL=256,File Size (bytes)=30720,
NOTE: Invalid data for Amount_outstanding in line 1 1-6.
1 CHAR ÐÏ.à¡± 6
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.
08-28-2014 10:29 AM
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.
08-28-2014 10:35 AM
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:
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;
libname test clear; *clears your connection to the excel file;
Export -- basically the same but the data step is reversed.
See if any of this helps!