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
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:
Data want;
set have;
RETAIN flag 0;
if missing(column2) then flag=1;
if flag=1 then delete;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.