Help using Base SAS procedures

PROC Import specificities excel file

Reply
Contributor
Posts: 45

PROC Import specificities excel file

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

Super User
Posts: 17,818

Re: PROC Import specificities excel file

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; 

Ask a Question
Discussion stats
  • 1 reply
  • 143 views
  • 0 likes
  • 2 in conversation