BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
gemi
Fluorite | Level 6

Hi, we have an annoying problem..

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

 

 

 

 

 

 

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

tab_sas.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

sas_img_yes.jpg

 

Exactly the same formatting of columns and rows !!

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

Please help us 😞

1 ACCEPTED SOLUTION

Accepted Solutions
HunterT_SAS
SAS Employee

@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:

HunterT_SAS_0-1670938570181.png

 

and then the export looks like this:

HunterT_SAS_1-1670938614046.png

 

 



View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

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

Data never sleeps
HunterT_SAS
SAS Employee

@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:

HunterT_SAS_0-1670938570181.png

 

and then the export looks like this:

HunterT_SAS_1-1670938614046.png

 

 



gemi
Fluorite | Level 6

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
SAS Employee

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
Fluorite | Level 6
Thank you!
Panagiotis
SAS Employee

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

Panagiotis_0-1670964099352.png

 

 

 

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
Fluorite | Level 6

@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
SAS Employee

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
Fluorite | Level 6

@Panagiotis I think SAS Viya because of this:
dd.png

 I search on my dashboard and I found this:

ddd.png

 

But I think I don't have the authorization 😞

dddd.png

 

Correct ?

Panagiotis
SAS Employee

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

 

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 2685 views
  • 2 likes
  • 4 in conversation