Get a Better Export to Excel

[gemi]

Hi, we have an annoying problem..

In our company we have a lot of reports made like this:

But when we try to export data to Excel, we have that terrible result:

Is it possible to get a better result? A result that mirrors the initial report exactly as it is. For example, before we switched to SAS, we used a crude but functional application which exported the report directly to Excel as we saw it:

Exactly the same formatting of columns and rows !!

Is there any way to achieve this? Maybe through some plugin... ?

Please help us 😞

[LinusH]

Probably there are room for improvement.

But if you want to prioritize user experience in Excel, I think SAS Office Analytics is at least one answer to that (it comes with plugins to Office applications).

[HunterT_SAS · Accepted solution]

@LinusH is correct - the SAS add in for Microsoft Office is the only way to achieve this for Viya 3.5 and below releases of SAS Visual Anaytics.

For Viya 4 though (Visual Analytics v2022.x and up), a feature was added to export crosstabs to excel exactly as they appear in the report. That feature is described here if you are interested:

https://go.documentation.sas.com/doc/en/vacdc/v_017/vareportdata/p0zfjjr681y96kn1ma5258d8o4td.htm#n0...

and this is what it looks like:

and then the export looks like this:

[gemi]

Thanks to both for quick reply.

Yes, we have Viya V.03.05. @HunterT_SAS , sorry, can you explain me how to get the SAS add in for Microsoft Office ?

[HunterT_SAS]

That would likely need to be a conversation with your SAS account representative. It needs to be licensed/ordered if you don't already have it.

More information in general can be found here:

https://support.sas.com/en/software/add-in-microsoft-office-support.html

[gemi]

Thank you!

[Panagiotis]

The easiest way is how @HunterT_SAS  answered.

However, one way you could do something similar is by using ODS EXCEL and the REPORT procedure. The REPORT procedure summarizes data, or presents detailed data, based on your need. I'm not a proc report expert, but here is some code. I created a fake detailed data table, then summarized it using proc report, then exported it to Excel.

%let path = /*Enter your file path for the Excel file */;
%let fileName = myExcel.xlsx;

******************************************;
* create a fake sample detailed table    *;
******************************************;
data testtable;
	infile datalines delimiter=',';
	input AnnoFiscale $ OrdineMese $ Jan Feb Mar Apr May Jun;
	datalines;
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, Fatturato_progr, 100, 200, 300, 400, 500, 600
F22-23, qta_progr, 10, 20, 30, 40, 50, 60
F22-23, Fatturato_progr, 100, 200, 300, 400, 500, 600
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, Fatturato_progr, 100, 200, 300, 400, 500, 600
F22-23, qta_progr, 10, 20, 30, 40, 50, 60
F22-23, Fatturato_progr, 100, 200, 300, 400, 500, 600
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, Fatturato_progr, 100, 200, 300, 400, 500, 600
F22-23, qta_progr, 10, 20, 30, 40, 50, 60
F22-23, Fatturato_progr, 100, 200, 300, 400, 500, 600
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, qta_progr, 10, 20, 30, 40, 50, 60
F21-22, Fatturato_progr, 100, 200, 300, 400, 500, 600
F22-23, qta_progr, 10, 20, 30, 40, 50, 60
F22-23, Fatturato_progr, 100, 200, 300, 400, 500, 600
;
run;




*********************;
* Create Excel file *;
*********************;
* Set colors *;
%let backgroundColor = lightgray;
%let textColor = black;

ods excel file="&path.\&fileName" 
		options(sheet_interval='none') /*put all results on one sheet for testing*/;

proc report data=testtable spanrows 
			style(column)=[color=&textColor]
			style(header)=[color=&textColor backgroundcolor=&backgroundColor];
	* What columns you want *;
	columns (AnnoFiscale OrdineMese Jan Feb Mar Apr May Jun);
	
	* Specify what each column does in the report *;
	define AnnoFiscale / group style=[backgroundcolor=&backgroundColor bordercolor=black];
	define OrdineMese / group  style=[backgroundcolor=&backgroundColor bordercolor=black];
run;

ods excel close;

Result

Resources:

PROC REPORT Doc

An Introduction to PROC REPORT

This is using something different, but might help if you can use it: The Armchair Quarterback: Writing SAS® Code for the Perfect Pivot (Table, That Is)

[gemi]

@Panagiotis thank you!
I'm a new user on SAS, so where can I use code with ODS EXCEL and REPORT procedure? I mean, where do I have to put that code? is there a programming environment on sas? Or should I use an external program?

[Panagiotis]

I apologize, I didn't realize you were new to SAS/SAS code. 

You need to execute that code one of the programming interfaces SAS offers. To start, do you have SAS Viya or traditional SAS? 

Also, this course is free and would help you get started with SAS programming: https://support.sas.com/edu/schedules.html?crs=PROG1&ctry=US. It's a great course with a lot of information. 

Creating that report is definitely doable. Just depends on your coding skill set. The code I sent above should work when you run it anywhere. It's a starting point.

- Peter

[gemi]

@Panagiotis I think SAS Viya because of this:

 I search on my dashboard and I found this:

But I think I don't have the authorization 😞

Correct ?

[Panagiotis]

Yes that's where you need to be. Looks like your account doesn't have access. Something you will have to work out with your SAS administrator. If you can eventually get access, you could start learning how to code.

SAS provides a free personal use of SAS Studio (a place to start coding) to learn SAS programming. It's not for commercial use, but you could use it for training purposes and start learning SAS code and practicing. Just make an account here: https://www.sas.com/en_us/software/on-demand-for-academics.html

Then take the free programming 1 course to start your journey: https://support.sas.com/edu/schedules.html?crs=PROG1&ctry=US

For a more hands on learning experience you can sign up for a class with a SAS instructor (not free). There should be a list of available times in the link above. You can work with your organization about trying to take one of those. Some individuals prefer live classes.

- Peter