07-16-2015 07:52 AM
I am reading an excel file which has footer note in the last row. Since footer note is different from the data/ columns structure. It is giving error in the log.
I tried using NOBS option, but it is not working with external file reading (it is working with SAS dataset)
Please help me to find out the solution so that SAS will read only to the row before the footer row
07-16-2015 07:57 AM
Standard answer for anything Excel = don't use Excel in the first place. Can you not remove this row before you try to import it, that would be the easiest way. Recommend however saving the file to CSV (or simliar) then you can process it as a normal text file. Here is an exact post for what you want, but it assumes a CSV file:
07-16-2015 10:14 AM
I'm assuming the header and footers are recognizable ? Why not delete them from the table after loading the dataset ?
Also do you use EG or Base ?
07-16-2015 10:21 AM
I am using SAS EG. I cannot make any changes in file.
I can make changes in program only.
Also, program is still working fine but it is showing error in the log because it reads the data in incorrect format ( because it is footer )
I want to remove error from the log
07-16-2015 10:56 AM
I think you cannot do this if you use the import wizard.
You could connect to the excelsheet via a library and then import the data via a query with a filter to exclude the records you do not want.
07-17-2015 02:30 AM
@Keith0001 I am using filename and infile statement to read the file from server. I am not using Import wizard or any utility of SAS EG.
07-17-2015 07:54 AM
You can't read a real Excel file with an INFILE statement. The INFILE statement is used for reading TEXT files (this statement is not 100% correct; take it as a rule of thumb). I assume what you call Excel is actually a .csv (which is a text file).
To not read the last line of any text file the following should work:
infile .... end=last;
if last then delete;
Does this solve your problem?
07-17-2015 08:09 AM
If its a CSV file, then why would it have additional non-CSV data attached? If that's correct and it is a CSV, I would be even more tempted to go back to the vendor and ask why they are supplying non-conformant data.
07-17-2015 08:27 AM