PROC Import specificities excel file

[Planck]

Hi all,

I'm trying to import some specific type of Excel file into SAS 9.4.

Specific because nothing is done very easily and I can't ask the provider of the file to change it 😕

Let say that my Excel file has a number of column equals to "columnnumber", and number of rows equals to "rownumber"

What I know:

- columnnumber can change from one file to each other, but always > 10

- rownumber can change from one file to each other

- the data fields name are on row 2, from column 2 to 10.

- the data I need to import is just below the data fields name (so it begins on row 3, from column 2 to 10)

- the data ending row can change and there can be a lot of false rows at the end of the excel file with nonsense things written or blanks.... The only thing I know: I need to stop recording on the first blank cell on column 2.

Do you know how I can do it?

Thanks

[Reeza]

You can use the idea noted here:

https://communities.sas.com/t5/Base-SAS-Programming/Proc-Import-Excel-File-with-data-in-second-row/m...

You may need to clean up the file after the import - if the trailing records get read in. First get the data in and then You can use the following:

Data want;

set have;

RETAIN flag 0;

if missing(column2) then flag=1;

if flag=1 then delete;

run;