BookmarkSubscribeRSS Feed
nikunjgattani
Obsidian | Level 7

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

nikunjgattani
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Keith0001
Calcite | Level 5

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 ?

nikunjgattani
Obsidian | Level 7

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

Keith0001
Calcite | Level 5

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.

nikunjgattani
Obsidian | Level 7

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

Patrick
Opal | Level 21

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 3079 views
  • 0 likes
  • 4 in conversation