BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SKP
Calcite | Level 5 SKP
Calcite | Level 5

Hi,

 

I'm SAS beginner and hoping I can get some help.

 

I created summary table with "Month" in column and "Employee ID" in row, but when I exported it to excel, "Month" moved back to row.

Does anyone know how to export the table into excel in the same format?

 

Thank you so much,

SKP

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

Yes, you'll have to use the ODS EXCEL open and close statements, and in between you paste your Summary Tables code (from the Code tab in your task output).  The result will be something like this:

 

/* add this */
ods excel file="c:\temp\report.xlsx";

/* Code from your Summary Tables task */
PROC TABULATE
DATA=WORK.SKP
	
	;
	
	VAR Amount;
	CLASS Month /	ORDER=UNFORMATTED MISSING;
	CLASS "Employee ID"n /	ORDER=UNFORMATTED MISSING;
	TABLE /* Row Dimension */
'Employee ID'n*
  Sum={LABEL=""},
/* Column Dimension */
Month*
  Amount 		;
	;

RUN;

/* add this */
ods excel close;

 

And the beautiful result in Excel:

 

report.jpg

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

12 REPLIES 12
LinusH
Tourmaline | Level 20

How did you create the summary table, and how did you export it? With SAS code, or any wizard (like in SAS Studio/EG...).

Data never sleeps
SKP
Calcite | Level 5 SKP
Calcite | Level 5

Hi LinusH,

 

Thank you for your reply.

 

I created the summary table with Summary Table Wizard, and exported "Output Data" by doing Export -> Export Summary Tabe for XXX As A Step In Project.

 

Thanks,

SKP

ballardw
Super User

Export is for more for data interchange which would have a different appearance than you might expect.

I suspect you are looking for a Report based on your summarized data.

SKP
Calcite | Level 5 SKP
Calcite | Level 5

ballarbw,

 

Yes, that's correct.  I'm trying to export the report in excel which looks like my summarized data in SAS.

 

Thanks,

SKP

ChrisHemedinger
Community Manager

If you're okay with a little code, you can use ODS EXCEL to capture your report in an Excel spreadsheet (requires SAS 9.4m2 or later).

 

If you have the very latest version of SAS Enterprise Guide (v7.12), then Excel is a destination option in your Tools->Options->Results settings.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
LinusH
Tourmaline | Level 20

Enterprise Guide, right?

I have 6.1, but can't see "output data" anywhere.

In the export wizard, I don't have the Excel option. Do you, or did you use XML/HTML?

When I use HTML the report layout is preserved.

Data never sleeps
SKP
Calcite | Level 5 SKP
Calcite | Level 5

I haveEnterprise Guide 7.1.

After I select "Export Summary Table for XXX As A Step In Project", I can select XLSX in 2nd step.

I don't see HTML option.

But I would need to tweak it after exporting it, so prefer to use excel.

 

I included a couple screen shots where I can select XLSX option in the attachement.

 

Thank you.

SKP

ChrisHemedinger
Community Manager

If you're using SAS Report output (looks like you are), you can copy the objects from your Results view and paste them in Excel.  It looks pretty good -- usually.

 

But if you want to automate that, you'll probably be happier with ODS EXCEL as I mentioned in another reply.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
SKP
Calcite | Level 5 SKP
Calcite | Level 5

Hi Chris,

 

Can you show me in the code where I can specify the summary table I want to export?

I tried it, but it created the excel file with the example table, not mine...

 

Thanks,

SKP

ChrisHemedinger
Community Manager

Yes, you'll have to use the ODS EXCEL open and close statements, and in between you paste your Summary Tables code (from the Code tab in your task output).  The result will be something like this:

 

/* add this */
ods excel file="c:\temp\report.xlsx";

/* Code from your Summary Tables task */
PROC TABULATE
DATA=WORK.SKP
	
	;
	
	VAR Amount;
	CLASS Month /	ORDER=UNFORMATTED MISSING;
	CLASS "Employee ID"n /	ORDER=UNFORMATTED MISSING;
	TABLE /* Row Dimension */
'Employee ID'n*
  Sum={LABEL=""},
/* Column Dimension */
Month*
  Amount 		;
	;

RUN;

/* add this */
ods excel close;

 

And the beautiful result in Excel:

 

report.jpg

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ChrisHemedinger
Community Manager

Oh, I should also point out: because your Month var is a character (not a SAS date variable), you'll see it's not sorted in "month" order.  You might want to convert that to a SAS date using an INFORMAT to read it in.  That's another small tutorial -- but you'll find it gives you more reporting flexibility (roll the reporting up to Quarter or Year).

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
SKP
Calcite | Level 5 SKP
Calcite | Level 5

Chris,

 

It worked and I have the beautiful excel result!

Thank yo so much for your help!

 

SKP

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 14028 views
  • 0 likes
  • 4 in conversation