topic Re: displaying excel sheet from right to left in ODS and Base Reporting

displaying excel sheet from right to left

Sivan — Sun, 13 Jan 2013 12:00:49 GMT

Hi,

I want to create a multiple sheet excel file, is it possible to display the sheets from right to left?

Here's the code I'm using :

options nocenter;

ods listing close;

ods tagsets.ExcelXP path="********"

file="*****.xls"

style=printer;

/*sheet 1*/

ods tagsets.ExcelXP options(sheet_name='****' absolute_column_width='12' orientation='landscape' embedded_titles='yes');

title bold height=3 underlin=1 "********";

proc tabulate ……

/*sheet 2*/

ods tagsets.ExcelXP options(sheet_name='*****' orientation='landscape' embedded_titles='yes');

title bold height=3 underlin=1 "*******";

proc print …

ods tagsets.ExcelXP close;

Thank you for your help!

Sivan

Re: displaying excel sheet from right to left

Cynthia_sas — Sun, 13 Jan 2013 17:04:59 GMT

Hi:

I don't really understand what you mean by "right to left"? Do you mean the values within the cells being displayed with a normally right to left font? do you mean displaying Sheet2 on top of Sheet1? Or, if you have 5 columns, name, age, sex, height, weight, do you mean that you want to display the column in "right to left" order: weight, height, sex, age, name?

One question or thought that I have is -- is this something you can do in Excel, without SAS being involved? In other words, can you open a spreadsheet in Excel and do your "right to left" display? If so, can you then save that spreadsheet as Microsoft Office XML for Office 2003 (the xml choice in the Save As dialog?

I see your code, but since I don't know what you mean by "right to left", it's impossible to answer except for guessing.

cynthia

Re: displaying excel sheet from right to left

Vince_SAS — Wed, 23 Jan 2013 01:55:55 GMT

If you mean right-to-left language support, then I suggest you first get your code working with the HTML destination. Once you get that correct you can try using the ExcelXP tagset. I don't see any tagset options that are language-direction specific. I'm guessing that this requires a combination of a localized version of SAS and a localized version of Excel, but never having done this it's just a guess.

This document may be useful:

http://office.microsoft.com/en-us/excel-help/right-to-left-language-features-HA010354392.aspx

Vince DelGobbo

SAS R&D

Re: displaying excel sheet from right to left

Sivan — Wed, 23 Jan 2013 10:11:39 GMT

Thank you for your answer.

About your suggestion to use HTML - here attached is a code I'm using for HTML, the code creates an excel file from right to left as I want,

but I neet to create a multiple sheet excel file, is it possible to do this with HTML?

ods html style=Theme file = "\\path...\file_name..xls";

ods escapechar="^";

title1 bold height=3 underlin=1 "title...";

title2;

title3;

footnote;

proc print data=data_name label noobs split='*'

style(header) = {background=grayc3 font_size=2.5};

var

xxx

;

label

xxxxxx

;

run;

ods html close;

Re: displaying excel sheet from right to left

Vince_SAS — Wed, 23 Jan 2013 14:01:40 GMT

What is the result when you rerun your code, replacing "html" with "tagsets.ExcelXP" in the 2 ODS statements, and then open the resulting file using Excel?

Also what is the date and version number of the tagset? That information is printed to the SAS log each time the tagset is used.

Vince DelGobbo

SAS R&D

Re: displaying excel sheet from right to left

Sivan — Wed, 23 Jan 2013 14:49:01 GMT

I tried what you said, in this case the file created is left to right...

Here is the part in the log that I think is relevant to what you asked :

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011). Add options(doc='help') to the ods statement for more information.

Re: displaying excel sheet from right to left

Vince_SAS — Wed, 23 Jan 2013 16:46:25 GMT

It appears that ExcelXP does not support R-T-L. Can you go back to using the HTML destination, open the HTML file in Excel, verify that the output is R-T-L, select File > Save As > "XML Spreadsheet 2003 (*.xml)", and then attach the resulting XML file here so we can inspect it? That will help us to get R-T-L support into ExcelXP.

Going back to HTML, the MSOffice2K_X tagset generates HTML output and supports multiple worksheets, so you can try that.

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

Vince DelGobbo

SAS R&D

Re: displaying excel sheet from right to left

Sivan — Thu, 24 Jan 2013 12:32:12 GMT

Regarding the file you asked me to attach - I can not attach files because of security issues, can I email it to you?

And as for using an HTML output to create multiple worksheets -

I tried that, the HTML files are created but the Excel file does not,

here attached is the code I used and the log.

Thank you very much for your help!

/***my code***/

proc sort data=sashelp.class out=test;

by age;

run;

ods tagsets.msoffice2k path="\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly" file="temp.html" newfile=output;

proc print data=test;

by age;

run;

ods tagsets.msofice2k close;

ods tagsets.msoffice2k_x file="multiple.xls" style=statistical

options( worksheet_source="11#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp.html,

12#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp1.html,

13#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp2.html,

14#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp3.html,

15#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp4.html,

16#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\temp5.html"

);

data _null_;

file print;

put "test";

run;

ods tagsets.msoffice2k_x close;

/***log***/

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Program';

4 %LET _CLIENTPROJECTPATH='';

5 %LET _CLIENTPROJECTNAME='';

6 %LET _SASPROGRAMFILE=;

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGHTML TEMP;

12 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=Analysis

12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/4.3/Styles/Analysis.

12 ! css")

12 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v92/sasgraph.exe#version=9,2")

12 ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation;

NOTE: Writing HTML(EGHTML) Body file: EGHTML

14 GOPTIONS ACCESSIBLE;

15 ods tagsets.msoffice2k_x file="multiple.xls" style=statistical

16 options(

16 ! worksheet_source="11#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status_zero_monthly\t

16 ! emp.html,

17 12#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status

17 ! _zero_monthly\temp1.html,

18 13#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status

18 ! _zero_monthly\temp2.html,

19 14#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status

19 ! _zero_monthly\temp3.html,

20 15#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status

20 ! _zero_monthly\temp4.html,

WARNING: The quoted string currently being processed has become more than 512 characters long. You

might have unbalanced quotation marks.

21 16#\\icc-tlv\root\dwh\BOXI\I_SHAARO\Auth_Statistics\status

21 ! _zero_monthly\temp5.html"

22 );

WARNING: Tagset TAGSETS.MSOFFICE2K_X not found; the default tagset will be used instead.

NOTE: Writing TAGSETS.DEFAULT Body file: multiple.xls

24 data _null_;

25 file print;

26 put "test";

27 run;

NOTE: 1 lines were written to file PRINT.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.00 seconds

29 ods tagsets.msoffice2k_x close;

2 The SAS System 18:29 Monday, January 21, 2013

34 GOPTIONS NOACCESSIBLE;

35 %LET _CLIENTTASKLABEL=;

36 %LET _CLIENTPROJECTPATH=;

37 %LET _CLIENTPROJECTNAME=;

38 %LET _SASPROGRAMFILE=;

40 ;*';*";*/;quit;run;

41 ODS _ALL_ CLOSE;

44 QUIT; RUN;

Re: displaying excel sheet from right to left

Vince_SAS — Thu, 24 Jan 2013 14:37:58 GMT

You can email the file to wwwsupport@sas.com and mention in the body of the message that it should be forwarded to me.

The warning about the MSOffice2K_X tagset not being found indicates that either it's not installed on your system, or it is installed but not referenced in the current ODS path.

The second sentence of the "Introduction" section provides information that you can use to install the tagset.

Vince DelGobbo

SAS R&D

Re: displaying excel sheet from right to left

Tamar — Tue, 24 Dec 2013 13:48:52 GMT

I know it's been almost a year since this discussion started but I had the same problem recently and wanted to share the solution I found.

MIA Computers, SAS's representative in Israel, created an updated version of the ExcelXP tagset that includes the option sheet_layout="RTL". It can be downloaded from here (Hebrew only):
http://www.miac.com/cp31_תמיכה-בעברית.aspx

Hope this helps someone in the future!

Re: displaying excel sheet from right to left

EyalGonen — Thu, 07 May 2020 07:21:08 GMT

Hi Sivan,

Join our *brand new* SAS community for Israel users at this link: https://communities.sas.com/t5/SAS-Users-Group-in-Israel/gp-p/Israel we will be happy to give you insights on how to create RTL Excel sheets!

Eyal