BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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
10 REPLIES 10
Cynthia_sas
SAS Super FREQ

Hi:

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

cynthia

Cynthia_sas
SAS Super FREQ

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
sunilreddy
Fluorite | Level 6

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
Cynthia_sas
SAS Super FREQ

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

data_null__
Jade | Level 19

What version of the excelxp tag set are you using?

sunilreddy
Fluorite | Level 6

Any proc to check version of the excelxp tag

Cynthia_sas
SAS Super FREQ

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

sunilreddy
Fluorite | Level 6

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.

Reeza
Super User

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

Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2517 views
  • 0 likes
  • 5 in conversation