Help using Base SAS procedures

How to export summary table to excel in the same format

Accepted Solution Solved
Reply
Contributor SKP
Contributor
Posts: 25
Accepted Solution

How to export summary table to excel in the same format

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


Accepted Solutions
Solution
‎03-03-2016 08:15 PM
Community Manager
Posts: 2,761

Re: How to export summary table to excel in the same format

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

View solution in original post


All Replies
Super User
Posts: 5,256

Re: How to export summary table to excel in the same format

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
Contributor SKP
Contributor
Posts: 25

Re: How to export summary table to excel in the same format

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

Super User
Posts: 10,490

Re: How to export summary table to excel in the same format

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.

Contributor SKP
Contributor
Posts: 25

Re: How to export summary table to excel in the same format

ballarbw,

 

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

 

Thanks,

SKP

Community Manager
Posts: 2,761

Re: How to export summary table to excel in the same format

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.

Super User
Posts: 5,256

Re: How to export summary table to excel in the same format

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
Contributor SKP
Contributor
Posts: 25

Re: How to export summary table to excel in the same format

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

Community Manager
Posts: 2,761

Re: How to export summary table to excel in the same format

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.

Contributor SKP
Contributor
Posts: 25

Re: How to export summary table to excel in the same format

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

Solution
‎03-03-2016 08:15 PM
Community Manager
Posts: 2,761

Re: How to export summary table to excel in the same format

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

Community Manager
Posts: 2,761

Re: How to export summary table to excel in the same format

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).

Contributor SKP
Contributor
Posts: 25

Re: How to export summary table to excel in the same format

Chris,

 

It worked and I have the beautiful excel result!

Thank yo so much for your help!

 

SKP

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1122 views
  • 0 likes
  • 4 in conversation