SAS Enterprise Guide: Import Odd Spreadsheet Data
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!
Update: I’m using MS Excel 2010 and Enterprise Guide 5.1. There were some issues noted with Excel and EG that are discussed here: http://support.sas.com/kb/43/
Never miss a BI Notes post!
Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
Latest posts by Tricia Aanderud (see all)
- Seven Interesting Data Storytelling Examples - 2018-06-09
- Use Network Analysis to Understand Your Customers with SAS Visual Analytics 8.2 - 2018-01-21
- My 7 Favorite Features in SAS Visual Analytics 8.2 on Viya - 2018-01-14
- Designing Dashboards: Finding the Fantastic Five Colors - 2017-06-19
- Creating a Web Analytics Report in SAS Visual Analytics 8.1 - 2017-06-19