importing uneven data

ssas — Wed, 21 Jan 2009 15:03:12 GMT

Hi,
i have an excel file like this
Full Name Phone Event Event Date Hall Area Row Seats Price Price Level Price Types

Transaction No. Transaction Date Transaction Type User Name: Status: Number of Tickets: Total Amount

361640 12/04/2008 14:24 Phone Holland, P Sales 6 $95.00

Smith S 235977 Cinderella 2008 21/12/2008 14:00 The Central Theatre Balcony A 32-33 18.5 Band A Standard Clearing
Smith S 235977 Cinderella 2008 21/12/2008 14:00 The Central Theatre Balcony A 34-35 15.5 Band A Senior Citizen Clearing
Smith S 235977 Cinderella 2008 21/12/2008 14:00 The Central Theatre Balcony A 36-37 13.5 Band A Under 16 Clearing
Transaction No. Transaction Date Transaction Type User Name: Status: Number of Tickets: Total Amount

361678 12/04/2008 17:50 Phone Lycett, S Sales 14 $220.00

Allen A 07913 037009 Cinderella 2008 24/12/2008 14:00 The Central Theatre Balcony A 32-34 15.5 Band A Senior Citizen Clearing
Allen A 07913 037009 Cinderella 2008 24/12/2008 14:00 The Central Theatre Balcony A 35-39 18.5 Band A Standard Clearing
Allen A 07913 037009 Cinderella 2008 24/12/2008 14:00 The Central Theatre Balcony A 40-41 13.5 Band A Under 16 Clearing
Allen A 07913 037009 Cinderella 2008 24/12/2008 14:00 The Central Theatre Balcony B 32-35 13.5 Band A Under 16 Clearing
Transaction No. Transaction Date Transaction Type User Name: Status: Number of Tickets: Total Amount

361686 12/04/2008 18:41 internet Internet Sales 3 $50.50

HOW can i import into sas with the following output

Full Name Phone Event Event Date Hall Area Row Seats Price Price Level Price Types Transaction No. Transaction Date Transaction Type User Name: Status: Number of Tickets: Total Amount

smith alison 23533 cinderella 12/12/2009 10.30 central theatre balcony A 13-14 10.00 bandA clearing 12132 15/05/2008 card scott paid 2 20.00

john martin 23253 cinderella 12/12/2009 13.30 central theatre balcony A 44-52 12.00 bandA clearing 12132 15/05/2008 card scott paid 8 96.00

Re: importing uneven data

RichardH_sas — Wed, 21 Jan 2009 20:55:37 GMT

If I'm understanding this correctly, your Excel data has one or more rows for each transaction and price type, then one row with the summary charge in that transaction. Something like...

*Standard
*Senior
TOTAL
*Standard
TOTAL
*Standard
*Under 16
*Senior
TOTAL

Sound correct? If so, I would write some DATA step code to read this file. Do a Save As from Excel as a CSV or Tab-delimited file and then read in the result with a DATA step. The name for these types of raw data files is hierarchical, and there's an example of code to read one of them here...

http://support.sas.com/kb/24/654.html

There's also alternative code from Warren Repole here...

http://www.repole.com/dinosaur/hierarchical.html

I can't think of a way to use the importing choices in Enterprise Guide to read this in since it involves mixed record types. You could fool around with the Excel file up front as an alternative, but unless you're an Excel grandmaster that would probably be more trouble than writing the DATA step code. Hope this helps!

topic importing uneven data in SAS Enterprise Guide

importing uneven data

Re: importing uneven data