topic Re: How to exclude footer/last row while reading excel file in SAS? in SAS Programming

How to exclude footer/last row while reading excel file in SAS?

nikunjgattani — Thu, 16 Jul 2015 11:52:00 GMT

Hi,

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

Re: How to exclude footer/last row while reading excel file in SAS?

RW9 — Thu, 16 Jul 2015 11:57:42 GMT

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:

Re: How to exclude footer/last row while reading excel file in SAS?

nikunjgattani — Thu, 16 Jul 2015 12:17:17 GMT

I cannot make any change in file because it present in different server. I have read access only

Re: How to exclude footer/last row while reading excel file in SAS?

RW9 — Thu, 16 Jul 2015 12:27:41 GMT

Open file in Excel, Save As to local file on your machine. Then you can edit the file.

Re: How to exclude footer/last row while reading excel file in SAS?

Keith0001 — Thu, 16 Jul 2015 14:14:10 GMT

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 ?

Re: How to exclude footer/last row while reading excel file in SAS?

nikunjgattani — Thu, 16 Jul 2015 14:21:57 GMT

Hi Keith,

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

Regards

Nikunj

Re: How to exclude footer/last row while reading excel file in SAS?

Keith0001 — Thu, 16 Jul 2015 14:56:18 GMT

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.

Re: How to exclude footer/last row while reading excel file in SAS?

nikunjgattani — Fri, 17 Jul 2015 06:30:16 GMT

@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.

Re: How to exclude footer/last row while reading excel file in SAS?

Patrick — Fri, 17 Jul 2015 11:54:27 GMT

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:

data want;

infile .... end=last;

input ....;

if last then delete;

.....

run;

Does this solve your problem?

Re: How to exclude footer/last row while reading excel file in SAS?

RW9 — Fri, 17 Jul 2015 12:09:49 GMT

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.

Re: How to exclude footer/last row while reading excel file in SAS?

Patrick — Fri, 17 Jul 2015 12:27:14 GMT

My reply to the OP was based on the assumption that this is just some footer information to be excluded, eg. a last line with a check-sum, a record count or even something more simple.