topic Re: ODS excel output in ODS and Base Reporting

ODS excel output

sunilreddy — Wed, 26 Feb 2014 14:37:11 GMT

Hi,

I am trying to create multiple sheets in excel file, but its always displaying right justified for all character columns even i spefified with

style=[just=l];

Output file is atatched. Anything wrong with below sample code

tagsets.excelxp file="/opt/app/SAS/data/export/WUSS1.xls"

style=sasweb ;

tagsets.excelxp options(autofilter='ALL' sheet_name='Male');

proc report data=sashelp.class nowd;

where sex='M';

name sex;

name/display 'male' style=[just=l];

sex/style=[just=l];

;

tagsets.excelxp options(autofilter='ALL' sheet_name='FeMale');

proc report data=sashelp.class nowd;

where sex='F';

name sex;

name/display 'female' style=[just=l];

sex/style=[just=l];

;

ods tagsets.excelxp close;

Re: ODS excel output

Cynthia_sas — Thu, 27 Feb 2014 03:22:33 GMT

Hi:

Is your test syntax missing some keywords? I don't see DEFINE or ODS on some statements. Is this the whole program?

cynthia

Re: ODS excel output

Cynthia_sas — Thu, 27 Feb 2014 03:42:38 GMT

Hi:

One thing I notice is that you use the TABULATE form of the STYLE= override ... the simple STYLE={....} and although I know that the form CAN/MIGHT work in some instances, I find it better, in the long run, to use the syntax that is documented for PROC REPORT, which is (in the PROC and DEFINE statements) to use:

style(location)={...}

where location can be HEADER, COLUMN, REPORT, SUMMARY, etc. of course not all locations can be used in all statements, but in the DEFINE statement, I think it is best to say STYLE(COLUMN) if you want the column info to be left justified or center justified. In the simple version of your code below (for one group), I have made the cell width larger, so you can see that the headers are center justified while the data cells are left justified.

cynthia

** code below;

ods listing close;
ods tagsets.excelxp file="c:\temp\examp.xls" style=sasweb ;

ods tagsets.excelxp options(autofilter='all' sheet_name='Male');

proc report data=sashelp.class nowd
style(header)={just=c width=1in};
where sex='M';
column name sex;
define name/display 'male' style(column)=[just=l];
define sex/style(column)=[just=l];
run;

ods tagsets.excelxp close;

Re: ODS excel output

Jagadishkatam — Thu, 27 Feb 2014 04:00:53 GMT

I believe there are sytax missing , i tried the same code with updated missing syntax. i don't see any problem with the code and also the values are left aligned

ods listing close;

ods tagsets.excelxp file="~path/WUSS1.xls"

style=sasweb ;

ods tagsets.excelxp options(autofilter='ALL' sheet_name='Male');

proc report data=sashelp.class nowd;

where sex='M';

column name sex;

define name/display 'male' style=[just=l];

define sex/style=[just=l];

run;

ods tagsets.excelxp options(autofilter='ALL' sheet_name='FeMale');

proc report data=sashelp.class nowd;

where sex='F';

column name sex;

define name/display 'female' style=[just=l];

define sex/style=[just=l];

run;

ods tagsets.excelxp close;

output:

Thanks,

Jag

Re: ODS excel output

sunilreddy — Thu, 27 Feb 2014 08:16:58 GMT

I've tried with both of ur code also. I am still not seeing left aligned in excel 2010 version. I assume it might be a probelme while exporting into excel.

Pls find the attached excel.

Re: ODS excel output

Cynthia_sas — Thu, 27 Feb 2014 13:48:31 GMT

Hi:

At this point, your best resource is to open a track with Tech Support. While it is true that I am using Office 2013 to open my file, that should NOT make a difference in the syntax. When you run my code, you should be seeing the same justification. Since you are not doing a true "export" to Excel, but are merely creating an XML file for Excel to open, you should be creating XML with the correct instructions to left justify the cell text. Tech Support will be the best ones to help you.

cynthia

Re: ODS excel output

data_null__ — Thu, 27 Feb 2014 16:52:55 GMT

What version of the excelxp tag set are you using?

Re: ODS excel output

sunilreddy — Sun, 02 Mar 2014 20:37:02 GMT

Any proc to check version of the excelxp tag

Re: ODS excel output

Cynthia_sas — Sun, 02 Mar 2014 20:42:10 GMT

Hi:

You should be able to find the version number in the SAS log after your job runs. Look for NOTE: such as shown below, You will find the version number and date that the version was released. This is the version I used to take the screen shot previously posted..

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\examp.xml

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.

cynthia

Re: ODS excel output

sunilreddy — Sun, 02 Mar 2014 21:54:58 GMT

Thanks for ur clarification. I am using v1.94. Any suggestions to aviod right justification for all character variales.

MPRINT(TEST): ods tagsets.excelxp file='/opt/app/SAS/data/export/test_xp.xls' style=sasweb;

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

statement for more information.

Re: ODS excel output

Reeza — Sun, 02 Mar 2014 23:14:39 GMT

Download the latest version and check if that changes things.

Download the code from here and run it, you don't need install privileges or admin rights.

Page:

Base SAS: ODS MARKUP

Download Tagset link:

ExcelXP