12-29-2016 06:33 AM
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?
12-29-2016 06:49 AM
You can use the idea noted here:
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:
RETAIN flag 0;
if missing(column2) then flag=1;
if flag=1 then delete;