topic Re: Custom Report exported to Excel in .srx does not displays tables side by side in SAS Programming

Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Wed, 20 Apr 2022 10:49:28 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

Ksharp — Wed, 20 Apr 2022 12:47:18 GMT

https://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Wed, 20 Apr 2022 13:59:04 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ballardw — Wed, 20 Apr 2022 14:19:06 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Wed, 20 Apr 2022 14:42:20 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ballardw — Wed, 20 Apr 2022 14:54:03 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Wed, 20 Apr 2022 15:05:12 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ballardw — Wed, 20 Apr 2022 15:12:44 GMT

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.

Re: Custom Report exported to Excel in .srx does not displays tables side by side

Ksharp — Thu, 21 Apr 2022 12:14:06 GMT

Ha. Maybe you need install it .

https://support.sas.com/rnd/base/ods/odsmarkup/

Re: Custom Report exported to Excel in .srx does not displays tables side by side

Ksharp — Thu, 21 Apr 2022 12:18:03 GMT

Re: Custom Report exported to Excel in .srx does not displays tables side by side

Ksharp — Thu, 21 Apr 2022 12:28:32 GMT

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 :

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Thu, 21 Apr 2022 17:39:32 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ChrisHemedinger — Thu, 21 Apr 2022 18:27:13 GMT

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;

Re: Custom Report exported to Excel in .srx does not displays tables side by side

Ksharp — Fri, 22 Apr 2022 12:05:13 GMT

You can install this tagset as long as you can running sas code .

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Mon, 25 Apr 2022 08:13:24 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ChrisHemedinger — Mon, 25 Apr 2022 12:32:28 GMT

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

Re: Custom Report exported to Excel in .srx does not displays tables side by side

MART1 — Mon, 25 Apr 2022 13:47:29 GMT

Thanks @ChrisHemedinger

I now understand a bit better how this works.

Just to clarify, here is what I do to build this Excel pack:

- I use the Report Builder Interface to build a number of SAS reports;

- I then export them as .srx format and import in Excel using the SAS Add-In for MS Office. The reports are spread across a number of tabs (and work very well apart from this formatting issue).

I do so in order to have an automated process, and to have everything in one place (Excel).

I see exporting in Html keeps the formatting, but if I had all the outputs in html I'd need to combine all of those in one central place (like I do in Excel).

Using tagsets.MSOffice2k I can only see the html output - I don't see where the excel output is created? here is what I'm using

options sastrace=',,,d' sastraceloc=saslog NOSTSUFFIX;
OPTIONS FULLSTIMER;


 ods tagsets.msoffice2k_x 
file="%sysfunc(getoption(work))/output.xls" 
 options(panelcols="2") style=normal;


Title "Tables";


	PROC SQL;
		Title "Table 1";
		SELECT t1.Make, 
			t1.Model, 
			t1.Type, 
			t1.EngineSize
		FROM SASHELP.CARS t1
			WHERE MAKE IN ("Saab", "Volvo")
		;
	QUIT;



	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 tagsets.msoffice2k_x close;

again, many thanks

Re: Custom Report exported to Excel in .srx does not displays tables side by side

ChrisHemedinger — Mon, 25 Apr 2022 16:48:53 GMT

The file is in the WORK folder for your SAS session. EG should detect it and offer to download/open it for you in an external application:

You can also use the Copy Files task in EG to add a download step to your process flow to copy this file from the server to a local folder on your PC.