SAS Enterprise Guide: Import Odd Spreadsheet Data

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

Perhaps you took my advise a few weeks back and took advantage of the amazing (trumps blare!) SAS Enterprise Guide ability to import MS Excel spreadsheets. However, if your spreadsheet is a little different – then you may run into some issue when you go to import it. For instance, look at this import:

Oh it’s so horrible – the columns names are not there and it’s going to be a lot of work to clean it up. Urgh .. it’s giving me a bad day already. Looks like the default approach to importing this spreadsheet didn’t work out as well. Curses – there must be a better way!

Your Data Needs Some Special Lovin’

So let’s look at a spreadsheet similar to the one imported above. You’ll notice that the actual data I want starts on row 3 not on row 1 as the default import would like. So row 3 contains the column labels and row 4 contains the data.

When you go with the default…

If I go with the defaults and select First row of range contains the field name – the Import wizard default assumes row 1 is where I want to start. Basically when using this approach with data that is not as expected – I have to decide if I want to edit the field names now or later in the code. Since the import procedure used the first row – it’s all messy. This is not a good approach. It’s too much work – which I think should be illegal.

Use the Top-Left Cell Row Setting, Luke

You can set the top-row, which in this case is cell A3. Then SAS EG understands where the first row is located and the import is handled correctly. Check it out … SAS imports and you drink coffee (fruit juices are also allowed). If you plan to use this task more than once, use the Expand row range as needed. This setting causes the lower-right cell value to increase if more rows are added to your spreadsheet. It’s the cool method!