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

Reply
Contributor
Posts: 22

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

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

Super User
Super User
Posts: 7,942

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

Posted in reply to nikunjgattani

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:

Contributor
Posts: 22

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

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

Super User
Super User
Posts: 7,942

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

Posted in reply to nikunjgattani

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

Occasional Contributor
Posts: 13

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

Posted in reply to nikunjgattani

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 ?

Contributor
Posts: 22

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

Posted in reply to Keith0001

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

Occasional Contributor
Posts: 13

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

Posted in reply to nikunjgattani

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.

Contributor
Posts: 22

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

Posted in reply to Keith0001

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

Respected Advisor
Posts: 4,173

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

Posted in reply to nikunjgattani

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?

Super User
Super User
Posts: 7,942

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

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.

Respected Advisor
Posts: 4,173

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

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.

Ask a Question
Discussion stats
  • 10 replies
  • 758 views
  • 0 likes
  • 4 in conversation