Hey Reeza,
This is a chance to pull out my favorite rant about folks who put data in Excel, and then proceed to format the spreadsheet with blank rows or columns that they then squeeze into obliviion. Nothing like pulling in twice as many rows as there are observations, with a boatload of additional variables named F?? that are all null values. Thanks lab guys who think they are helping.
Steve Denham
Agreed, hidden columns and rows drive me up the wall.
Regarding the topic of cleaning the Excel file, I usually do this (although I admit this is rather inelegant).
Open the Excel file and press Ctrl + End to find the last cell that contains data. Suppose you find that it is in Column X Row 246.
Then, you can write your SHEET option as SHEET='Sheet11$a1:x246';
Depending on version of Excel the Ctrl-End will find the last row someone manipulated. That row could very well be completely blank. Also you may find yourself in a column way past actual data entered for any record, or at least visible.
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.