I need help in converting the attached excel data file to a sas data set. The attached excel file is a truncated .xls file as there are file size limitations for an attachment.
I am also attaching the sas program which i wrote but there are errors I am encountering.
would be grateful for help for pointing out the errors in the program given below.
best regards and thanks
Raman
PS: bas sas program code is given below. I had saved the excel file into a text file prior to submitting the below code
libname TEST1 'F:test ';
LRECL = 150;
data TEST1.K1_Out;
infile 'F:test\Kud_Data1.txt';
input
@1 SC 1 @9 TKTID 6 @17 CUSTID $ 8. @25 ProdID $ 10 @25 ProdID $ 10 @41 EMPID 6
@49 QTY 1 @59 Tax:5.2 @65 R_prix 7.2
@73 D_A 7.2 @81 D_Per 5.2 @89 T_Amt 8.2@89 T_Amt 8.2
@97 St_Time & $ 22 @122 B_Type $ 1
@130 M_Disc 4
@138 Oth_Disc 7.2
@147 Sal_St $ 1;
run;
How I convert often involves screaming and kicking.
First the code you are showing looks like reading a text file with fixed columns. Excel files are not text and really don't have any concept of fixed column width.
Generally I convert Excel files to CSV (File Save AS from Excel) and then use a data step to read the file. Often I use the import wizard, or Proc Import, for CSV setting a LARGE value for guessing rows. One advantage of this approach is that data step code to read the file is generated and can be saved and modified to be a "nicer" program by adding labels, use of custom informats/formats, changing variable names( things from Excel seem to often have names like CLIENT_PERSONAL_0_VOLUNTARILY_IN which I might want to shorten) to make more sense from column headings. Then if I have another of these files to read later I do the conversion to CSV, point the program to read the new file and write to a different SAS data set. Another advantage of this approach is I know what type is variable is and the characteristics. Reading directly from Excel often yeilds changes in columns from text to numeric, lengths of text variables and sometimes even the variable names.
You may not have set a large enough value for GUESSINGROWS if you used the wizard or Proc Import. By default onlyt the first 20 rows are "examined" to set properties of the file.
You could either go through the proc import or wizard and increase that value OR copy the code from the Log and modify it to have a larger LRECL parameter. Or set the informats for the character variables to your expectations.
Your sheet looks well formed.
Did you try just letting SAS convert it for you?
libname myxls xls 'path to xls file';
proc copy inlib=myxls outlib=work;
run;
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.