topic Re: ODS targets.excelxp in ODS and Base Reporting

ODS targets.excelxp

Smijoss — Fri, 07 Mar 2014 20:09:58 GMT

Hi,

I have an issue with ODS excelxp as shown in image below. The Char variable are set to justify right, even though i have a just=L in the proc report.

This is happening in a Unix SAS server. I copied the sas dataset to windows and there the same code sees to be working fine with ODS targerts.excelxp

The HTML View is showing it left justified, but I am not able to control it in excel

Proc report definition -

define GROUP_INDEX	/ DISPLAY 'GROUP INDEX' STYLE(column)={JUST=LEFT CELLWIDTH=0.6in };
define GROUP_NAME	/ display 'PID NAME' STYLE(column)={JUST=LEFT CELLWIDTH=2.8 in };
define ACCOUNT_NAME	/ display 'ACCOUNT NAME' STYLE(column)={JUST=LEFT CELLWIDTH=2.2in };

Re: ODS targets.excelxp

Reeza — Fri, 07 Mar 2014 21:21:33 GMT

are both using the same version of the tagset?

Re: ODS targets.excelxp

Cynthia_sas — Fri, 07 Mar 2014 22:05:55 GMT

Hi:

I don't know what you mean by "the HTML View is showing it left justified but I am not able to control it in excel" -- when you use TAGSETS.EXCELXP, you are creating an XML file, not an HTML file. Do you mean the automatic HTML file that you can get in Windows? I would expect your code to work on all servers, unless as Reeza suggests, the two servers are using different versions of the destination, although you can check that in the log. When I run the code below, my character variables are left justified (which is the default, so you really don't need the extra control). But if you run this code in Unix and get right-justified character variables, then your best resource is to work with Tech Support on a resolution. This output shows all the default justifications and I used TAGATTR to make sure that a large PIDNUM was formatted correctly.

Cynthia

data testit;

set sashelp.shoes;

index = stores;

acctname = region;

pidnum = int(sales*1377);

pidname = product;

insurance_type = upcase(substr(subsidiary,1,3));

run;

ods tagsets.excelxp file='c:\temp\testjust_default.xls' style=sasweb;

proc report data=testit(obs=15) nowd

style(header)={cellwidth=2in};

column index acctname pidnum pidname insurance_type;

define index / display 'Index=R';

define acctname / display 'Acctname = L';

define pidnum / display style(column)={tagattr="#########000"} 'Num=R';

define pidname / display 'PIDNAME= L';

define insurance_type / display 'I_T = L';

run;

ods tagsets.excelxp close;

Re: ODS targets.excelxp

Smijoss — Fri, 07 Mar 2014 22:37:09 GMT

Hi Cynthia ,

Thanks you for the Quic response, The added two Char variables to your code.

and both are coming out as left justified the the results screen but right justified on the excel sheet.

Please see the images below. Thanks .

data testit;

set sashelp.shoes;

index = stores;

acctname = region;

Char_var = "weekend is near";

pidname = product;

insurance_type = "This is test";

pidnum = int(sales*1377);

run;

ods tagsets.excelxp file='/apps/sas/datasets/data10/NAMSA92/TEST/zmsa_Smijo/testjust_default.xls' style=sasweb;

proc report data=testit(obs=15) nowd

style(header)={cellwidth=2in};

column index Char_var acctname pidnum pidname insurance_type;

define index / display 'Index=R';

define Char_var / display 'Acctname = L' style(column)={just=L};

define pidnum / display style(column)={tagattr="#########000"} 'Num=R';

define pidname / display 'PIDNAME= L';

define insurance_type / display 'I_T = L';

Title 'This is justify testing' ;

run;

ods tagsets.excelxp close;

Re: ODS targets.excelxp

Smijoss — Fri, 07 Mar 2014 22:45:51 GMT

Hi Reeza , Thank you for the response -

I tired with a recent tagset -

Still shows same result

29 ods tagsets.excelxp file='/apps/sas/datasets/data10/NAMSA92/TEST/zmsa_Smijo/testjust_default.xls' style=sasweb;

NOTE: Writing TAGSETS.EXCELXP Body file: /apps/sas/datasets/data10/NAMSA92/TEST/zmsa_Smijo/testjust_default.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013). Add options(doc='help') to the ods

statement for more information.

Re: ODS targets.excelxp

Smijoss — Fri, 07 Mar 2014 23:05:40 GMT

One more thing. out of curiosity i replaced proc report with proc print and i get the same issue -

data testit;

Char_var = "weekend is near";

run;

ods tagsets.excelxp file='/apps/sas/datasets/data10/NAMSA92/TEST/zmsa_Smijo/testjust_default.xls'

style=sasweb ;

proc print data=testit ;

Format Char_var $char50. ;

run;

ods tagsets.excelxp close;

Re: ODS targets.excelxp

Cynthia_sas — Sat, 08 Mar 2014 03:04:56 GMT

Hi: I am running this version by default:

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\testjust_default2.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.129, 11/07/2011). Add options(doc='help') to the ods
statement for more information.

And still all my character variables are left justified by default. But, since I wasn't running the same version as you reported, I updated my template and reran the code and the results of using version 1.130 are also attached. Still left justified in Windows. I do not have a Unix system to play on. But Tech Support can test your code on Unix. Did you open a track with Tech Support?

cynthia

data testit;
length char_var $50;
set sashelp.shoes(obs=5);
Char_var = "weekend is near";
run;

ods tagsets.excelxp file='c:\temp\testjust_default2.xls'
    options(embedded_titles='yes')
    style=sasweb   ;

proc print data=testit noobs;
title 'All character vars are left justified and all numeric vars are right justified';
run;

ods tagsets.excelxp close;

Re: ODS targets.excelxp

Smijoss — Wed, 12 Mar 2014 15:21:25 GMT

Cynthia,

Yes, I have raised a ticket with tech support team. They are working on it. I will update you once I have an answer. Thank you so much for your time and efforts to help.

Have a great day ahead ! thanks .

Re: ODS targets.excelxp

Smijoss — Fri, 28 Mar 2014 17:07:40 GMT

Reply from sas tech team :

The problem here appears to be the hot fix installed C0003 modified the the Base.Template.Style which is used as a

parent to all other styles. In this style that you sent me, if you scroll down to the Data, you will see that the Text-Align

style property was set to right. WE can modify the style and compile it and this will correct the problem for you. However, it SAS is installed on the Server, then we will need to compile this in SASHELP to see it change. You can add the below

ODS PATH statement to tell SAS add this to the WORK location or another location. Please let me know how this works after recompiling the below style

Run the following code -

proc template;

source Base.Template.Style / file="some directory\style.txt";

run;

Open the output file to see the Base.Template.Style

define style Base.Template.Style;

notes "Implicit parent for all style templates";

: searchfor below text and remove the align setting

style Data from Cell

"Default style for data cells in columns." / textalign = right;

The text alignment attribute abbove was causing the issue, remove it.

They gave me an update command,

ods path(prepend) work.templat(update);

%include "styles.txt";

/* your code */

But it didnot seem to work (probably it defaults to server) so i gave a new style with the same description and used it and it worked!.