topic Re: Ignoring specific rows in SAS in SAS Programming

Ignoring specific rows in SAS

rsuresh — Fri, 07 Jan 2022 21:32:10 GMT

I have a large excel file that I am importing to SAS; Row 1 has the variable names but rows 2-4 of the file have additional descriptive information (about the variables) that I do not want in my SAS dataset. Is there a way to exclude those 3 rows while importing or after importing the data onto SAS?

Re: Ignoring specific rows in SAS

PaigeMiller — Fri, 07 Jan 2022 21:33:24 GMT

Best thing is to remove these rows in Excel.

Re: Ignoring specific rows in SAS

ballardw — Fri, 07 Jan 2022 22:53:46 GMT

If you are going to have multiple files with the same structure then one approach is to save the file to a CSV file format and write a data step to read the file.

The Infile statement that is used by the data step to identify the file and characteristics such as delimiters and logical record length include a FIRSTOBS option which means skip to the row of the file before attempting to read anything.

One big reason to use the CSV approach with multiple files is so you control the type and length of all the variables. Proc Import, or any wizard that uses such, makes separate decisions for each file imported. Which means that variables of the same name may change type or length from different files. Or in a poor data source environment may even mean that the variable names change. The code to read CSV would treat the columns in order the same for each file.

If you save your file to CSV and use Proc Import for that file (recommend the guessingrows=max; option) then SAS will create data step code that appears in the log. You can copy that code to the editor, clean it up save and reuse.
This will work better with a file without extra information as you describe. Once you have a nice program to read the file you can add the option for Firstobs to skip the header rows for the next file.

Re: Ignoring specific rows in SAS

Tom — Sat, 08 Jan 2022 02:51:00 GMT

@rsuresh wrote:

I have a large excel file that I am importing to SAS; Row 1 has the variable names but rows 2-4 of the file have additional descriptive information (about the variables) that I do not want in my SAS dataset. Is there a way to exclude those 3 rows while importing or after importing the data onto SAS?

You are in luck. The quirky way that the DATAROW= statement works in PROC IMPORT with the XLSX db engine will do exactly that. You can tell it what row to start reading the data, but it always uses the first row to guess what names to give to the variables.

proc import datafile='large.xlsx' dbms=xlsx
  out=want replace
;
  datarow=5;
run;

If the extra information was in rows 1 to 3 and row 4 had the column headers then you would have to work harder.

Example program:

proc import datafile='c:\downloads\skip.xlsx' dbms=xlsx
  out=skip replace
;
  datarow=3;
run;

Result:

Obs    X             Y    Z

 1     1    12/31/2021    Height

Picture of XLSX file:

Re: Ignoring specific rows in SAS

rsuresh — Tue, 11 Jan 2022 03:10:24 GMT

This worked perfectly, thank you so much!

Re: Ignoring specific rows in SAS

rsuresh — Tue, 11 Jan 2022 03:19:34 GMT

Unfortunately, I have multiple files and most of them have varying formats for themselves and the sheets within as well.