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;
Hi:
Is your test syntax missing some keywords? I don't see DEFINE or ODS on some statements. Is this the whole program?
cynthia
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;
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.
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
What version of the excelxp tag set are you using?
Any proc to check version of the excelxp tag
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
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.
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:
Download Tagset link:
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.