Hello
I have a number of Custom Reports that I export to Excel in .srx format and are working very well (I use the SAS add-ins).
However when I have two tables side by side, Excel displays them one above the other, rather than next to each other.
Here is an example with mock data:
This is the code
Title "Tables"; ods layout gridded columns=2; ods region; PROC SQL; Title "Table 1"; SELECT t1.Make, t1.Model, t1.Type, t1.EngineSize FROM SASHELP.CARS t1 WHERE MAKE IN ("Saab", "Volvo") ; QUIT; ods region; PROC SQL; Title "Table 2"; SELECT t1.Make, t1.Model, t1.Type, t1.EngineSize FROM SASHELP.CARS t1 WHERE MAKE IN ("BMW", "Infiniti") ; QUIT; ods layout end;
and in SAS they are correctly displayed next to each other:
However when I export in.srx and import into Excel, it looks like the below:
I have looked into Properties, Settings ... but cannot find a way to have the tables side by side in Excel.
ps: not sure if it matters but I'm not using Proc Export (as SAS is on different servers than Excel); instead I use the "Export as a step In Project" function.
many thanks in advance
Thanks @Ksharp
unfortunately I'm using SAS 7 (soon moving to 8), and I see Tagsets.MSOffice2K_x is only available from SAS 9.
(I've tried just in case but I get the below)
ps: also I am not very familiar with ods, but I use the Step In project, rather than using file=...
Thanks
@MART1 wrote:
Thanks @Ksharp
unfortunately I'm using SAS 7 (soon moving to 8), and I see Tagsets.MSOffice2K_x is only available from SAS 9.
You might be using Enterprise Guide in some flavor of version 7 but ODS anything, much less region opitons pretty much did not exist with SAS 7 which was replaced 20 years ago.
ODS does not use dot notation as you show it. It would be:
ODS Tagsets.msoffice2k
if available.
Please save yourself some headaches posting LOG text. Copy the text from the log and on the forum open a text box using the </> icon that appears above the message window and then paste text. I, and many others, are not going to want to retype blocks of text to make a minor correction when given a picture and not text.
thanks @ballardw
noted.
For completeness, here is the LOG - without using the dot
I now receive
ERROR: No body file. TAGSETS.MSOFFICE2K output will not be created.
(I assume due to using SAS 7 instead of 9 or later)
thanks
1 The SAS System 08:06 Wednesday, April 20, 2022 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program (2)'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HtmlBlue 17 STYLESHEET=(URL=file:///D:/USR/LOCAL/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css) 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 25 GOPTIONS ACCESSIBLE; 26 options sastrace=',,,d' sastraceloc=saslog NOSTSUFFIX; 27 OPTIONS FULLSTIMER; 28 29 ods tagsets.msoffice2k style=normal 30 options(panelcols="2"); ERROR: No body file. TAGSETS.MSOFFICE2K output will not be created. 31 32 Title "Tables"; 33 34 34 ! PROC SQL; 35 Title "Table 1"; 36 SELECT t1.Make, 37 t1.Model, 38 t1.Type, 39 t1.EngineSize 40 FROM SASHELP.CARS t1 41 WHERE MAKE IN ("Saab", "Volvo") 42 ; 43 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.09 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 5335.15k OS Memory 32172.00k Timestamp 20/04/2022 03:39:21 PM Step Count 19 Switch Count 1 Page Faults 33 Page Reclaims 427 2 The SAS System 08:06 Wednesday, April 20, 2022 Page Swaps 0 Voluntary Context Switches 39 Involuntary Context Switches 4 Block Input Operations 1904 Block Output Operations 8 44 45 46 46 ! PROC SQL; 47 Title "Table 2"; 48 SELECT t1.Make, 49 t1.Model, 50 t1.Type, 51 t1.EngineSize 52 FROM SASHELP.CARS t1 53 WHERE MAKE IN ("BMW", "Infiniti") 54 ; 55 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 5335.00k OS Memory 32172.00k Timestamp 20/04/2022 03:39:21 PM Step Count 20 Switch Count 1 Page Faults 1 Page Reclaims 39 Page Swaps 0 Voluntary Context Switches 24 Involuntary Context Switches 1 Block Input Operations 64 Block Output Operations 0 56 57 58 /*ods tagsets.msoffice2k options(panelcols="2");*/ 59 60 ods tagsets.msoffice2k close; 61 62 GOPTIONS NOACCESSIBLE; 63 %LET _CLIENTTASKLABEL=; 64 %LET _CLIENTPROCESSFLOWNAME=; 65 %LET _CLIENTPROJECTPATH=; 66 %LET _CLIENTPROJECTPATHHOST=; 67 %LET _CLIENTPROJECTNAME=; 68 %LET _SASPROGRAMFILE=; 69 %LET _SASPROGRAMFILEHOST=; 70 71 ;*';*";*/;quit;run; 72 ODS _ALL_ CLOSE; 73 74 75 QUIT; RUN; 76
Most of the ODS destinations need to be told what to name the output file and it is a good idea to provide exactly where to place the file.
Since you seem to want an Excel file I would definitely not try to write to a TEMP file. Give a path and external filename that Excel will use like:
File="C:\folder\myfile.xlsx" on the Ods destination statement.
I would also suggest using minimal changes until you get the basic output you want. Then start adding in things like changing Styles. General process for a very long time is 1) get the content correct 2) then make it "pretty".
Thanks again @ballardw
All makes sense; however I cannot specify a file name as I have "Insufficient authorization to access...".
The only way I can export is using the Step In project (i.e. cannot use Proc export; I was told it's because in my company SAS is on a stand alone server, while all the drives are on other servers).
Also I'm creating a number of .srx report to populate the same Excel workbook on different tabs (using the SAS add-in); it's working really well apart from this issue.
many thanks
Access, permissions and locations are a thing for you and your Admin to work out.
The example I wrote was just to show something. Likely your SAS is executing on a server and you shouldn't have access to the C (or equivalent drive). But your SAS admin should be able to provide a location you can write to and what the path should look like and set permissions if they aren't already in place.
Your previous code was showing writing to a TEMP file. Which are just that, temporary, and go away when the fileref is cleared, such as the end of a SAS session. So really not a good choice to output a file you expect to open in another application and apparently the tagset code may be checking to see if the file was "valid" for creation.
Firstly Open the msoffice2k_x.sas code (check attachment) and running it to install msoffice2k_x tagset.
Secondly Running the following code:
ods tagsets.msoffice2k_x file="c:\temp\ppanels.xls" options(panelcols="3") style=normal; proc print data=sashelp.class;run; proc print data=sashelp.class;run; proc print data=sashelp.class;run; ods tagsets.msoffice2k_x options(panelcols="2") ; proc print data=sashelp.class;run; proc print data=sashelp.class;run; ods tagsets.msoffice2k_x close;
And I got this :
Thanks @Ksharp
it would be great but my organisation does not allow me to install anything external - (when I tried to recreate the code yesterday I clearly missed the fact I had to install it!)
thanks
When running in EG you likely cannot access the C: drive with SAS code, as it's often running on a remote session. Instead, specify a temp file location and EG will bring it down for you to open. Ex:
ods tagsets.msoffice2k_x file="%sysfunc(getoption(work))/output.xls"
options(panelcols="3") style=normal;
Many thanks @ChrisHemedinger and @Ksharp
(you are right @Ksharp - I can indeed install the code - i.e. run it).
When I use specify the below path (as I cannot export to any drive)
file="%sysfunc(getoption(work))/output.xls"
it gives me the output I need, however I don't see the option to export it in .srx format (and I need to to so because this code will be part of other reports being exported, which will be included in a "pack" in Excel).
The only option is .html. Probably .xls would be good too, but it's not an option when I export using the "Export as a Step in Project" (which is the only option I have to export automatically).
Many thanks
The .srx format is a SAS proprietary file type (SAS Report) that can be read by SAS Enterprise Guide, SAS Add-In for Microsoft Office, and SAS Web Report Studio. It was developed to allow for integration of content across all of these applications. It's power lies in the way you can manipulate the layout of the report and retain that layout even as you refresh the content, because the "SAS objects" that make up the content are identified and easily reconnected to the SAS instructions that produced them in the first place. It can also be exported to HTML, and/or copied to the Windows clipboard and pasted as HTML in non-SAS applications.
That said, the .srx format isn't usable outside of SAS and so in later versions, SAS Enterprise Guide uses HTML5 as its default output.
In the context of a SAS Enterprise Guide project, you can use the Report builder interface (File->New->Report) to build a SAS report with the different pieces of output from tasks and programs, add titles and annotations, and arrange content in a grid layout.
Result:
You can then Share this output to Excel or any Microsoft Office client...if you have the SAS Add-In for Microsoft Office installed.
However, you cannot programmatically control the layout of an .srx file using ODS statements like ODS LAYOUT. Only the client applications like SAS EG or SAS Add-In can manipulate it. If you want to programmatically control the layout, then you must select another output destination like HTML5 or perhaps the tagsets.MSOffice2k (which offers some coarse-grained layout in the form of panels options).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: