The SAS Output Delivery System and reporting techniques

ODS excel output

Reply
Frequent Contributor
Posts: 115

ODS excel output

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;


ods excel.png
SAS Super FREQ
Posts: 8,818

Re: ODS excel output

Hi:

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

cynthia

SAS Super FREQ
Posts: 8,818

Re: ODS excel output

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;


xp_column_left_header_center.png
Frequent Contributor
Posts: 115

Re: ODS excel output

Hi


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.


out1.pngout2.png
SAS Super FREQ
Posts: 8,818

Re: ODS excel output

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

Respected Advisor
Posts: 3,786

Re: ODS excel output

What version of the excelxp tag set are you using?

Frequent Contributor
Posts: 115

Re: ODS excel output

Any proc to check version of the excelxp tag

SAS Super FREQ
Posts: 8,818

Re: ODS excel output

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

Frequent Contributor
Posts: 115

Re: ODS excel output

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.

Super User
Posts: 19,059

Re: ODS excel output

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

Trusted Advisor
Posts: 1,137

Re: ODS excel output

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

Thanks,
Jag
Ask a Question
Discussion stats
  • 10 replies
  • 953 views
  • 0 likes
  • 5 in conversation