BookmarkSubscribeRSS Feed
ssas
Calcite | Level 5
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
1 REPLY 1
RichardH_sas
SAS Employee
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 578 views
  • 0 likes
  • 2 in conversation