SAS 9.3 proc means problem

[SteveDenham]

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

[Reeza]

Agreed, hidden columns and rows drive me up the wall.

[GreggB]

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';

[ballardw]

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.