How to export summary table to excel in the same format

[SKP]

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

[LinusH]

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

[SKP]

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]

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]

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]

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.

[LinusH]

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.

[SKP]

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]

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.

[SKP]

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 · Accepted solution]

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:

[ChrisHemedinger]

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

[SKP]

Chris,

It worked and I have the beautiful excel result!

Thank yo so much for your help!

SKP