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

How to export summary table to excel in the same format

SKP — Thu, 03 Mar 2016 20:27:25 GMT

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

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

LinusH — Thu, 03 Mar 2016 20:52:37 GMT

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

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

SKP — Thu, 03 Mar 2016 20:56:39 GMT

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

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

ballardw — Thu, 03 Mar 2016 21:06:03 GMT

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.

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

SKP — Thu, 03 Mar 2016 21:08:13 GMT

ballarbw,

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

Thanks,

SKP

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

LinusH — Thu, 03 Mar 2016 21:12:46 GMT

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.

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

ChrisHemedinger — Thu, 03 Mar 2016 21:18:58 GMT

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.

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

SKP — Thu, 03 Mar 2016 21:25:29 GMT

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

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

ChrisHemedinger — Thu, 03 Mar 2016 21:32:27 GMT

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.

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

SKP — Thu, 03 Mar 2016 21:44:39 GMT

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

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

ChrisHemedinger — Thu, 03 Mar 2016 22:07:13 GMT

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:

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

ChrisHemedinger — Thu, 03 Mar 2016 22:10:33 GMT

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

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

SKP — Thu, 03 Mar 2016 23:05:26 GMT

Chris,

It worked and I have the beautiful excel result!

Thank yo so much for your help!

SKP