BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am creating a XML document using EXCELXP tagset in SAS on os/390 system.
Right now my tagset options look like this:
ODS TAGSETS.EXCELXP FILE=GERMANY
STYLE=CUSTOM
RS=NONE
OPTIONS(SHEET_NAME = 'Germany'
FROZEN_HEADERS='4' AUTOFILTER='ALL'
EMBEDDED_TITLES='YES'
ORIENTATION = 'LANDSCAPE'
SUPPRESS_BYLINES = 'YES')
style=Style.Newfonts;
It is creating a EXCEL document and it works fine. The problem happens when there exists German characters in the EXCEL sheet. I am able to see German characters on host file. How ever in the first line of XML document If I include encoding="windows-1251" after the version in this cell, then the German characters looks good when I download from host or FTP from host to any server. Can anyone please help what to do in my SAS program to write encoding in the XML document while using EXCELXP.
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest reviewing options with the SAS NLS (National Language Support) documentation/guide available on the SAS support website.

Scott Barry
SBBWorks, Inc.
Peter_C
Rhodochrosite | Level 12
Raju
on which version of the tagset is your system operating?
Installed with a production release of SAS913 on zOS are out-of-date features of excelXP that have been much enhanced after that distribution. On my windows SAS92 the note produced is[pre]NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12)[/pre]I expect it to be different on your zOS, but it must be updated if you are still operating with a version before v1.7. The summary of enhancements can be seen at http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html .

I hope you don't need to update your mainframe tagsets.excelxp, because that process caused me a lot of trouble. My [long-winded 😉 ] discovery of a solution can be seen in the earlier message on the forum, at http://support.sas.com/forums/thread.jspa?threadID=2011


Good Luck
PeterC
Robert_Bardos
Fluorite | Level 6
Been quite some time since I raised my voice on any SAS forum. Experimenting with EXCELXP on z/OS with SAS 9.1.3 this thread caught my attention. Not only this. It actually helped me solve my problem. That is, I am able to produce a multisheet excel file (stored in a zFS file and served through z/OS's web server) with data, labels and even sheet names containing german umlauts.

Let the code speak for itself:
/*------- top of sample code ------*/
x 'umask 002' ;
%include tgs(excelxp) / nosource2 ;
%sysfunc(ifc(&syscc ge 4,%str(endsas;),)) ;
ods listing close ;

%let _dt = %sysfunc(date(),deudfwkx.) ;
%let _tm = %sysfunc(time(),time8. ) ;

%let _fn1 = %sysfunc(getoption(nlscompatmode)) ;
%let _fn2 = %sysfunc(getoption(locale,keyword)) ;
%let _fn3 = %sysfunc(getoption(encoding,keyword)) ;
%let _fn4 = %sysfunc(getoption(dflang,keyword)) ;

data myclass ;
set sashelp.class ;
if name='Barbara' then name='Bärbel' ;
if name='Joyce' then name='Joëlle' ;
label name = 'Vorname'
sex = 'Geschlecht'
age = 'Alter'
height = 'Größe'
weight = 'Gewicht'
;
run ;
title1 h=2 'ExcelXP Tagset MultiSheet Sample' ;
title2 h=1 "erstellt am &_dt, um &_tm Uhr" ;
footnote1 h=5pt "&_fn1 / &_fn2 / &_fn3 / &_fn4" ;

ods tagsets.excelxp file="/srv/sas/tmp/sample_xclxp001.xls"
encoding='ISO-8859-1'
record_separator='0d'x
style=styles.sasweb2
;

ods tagsets.excelxp options
( autofilter = '2-3'
center_horizontal = 'yes'
center_vertical = 'yes'
embedded_footnotes = 'yes'
embedded_titles = 'yes'
frozen_headers = '4'
frozen_rowheaders = '1'
zoom = '180'
) ;

ods tagsets.excelxp options(sheet_name='Mädels') ;

proc print data=myclass noobs label ;
where sex='F' ;
run;

ods tagsets.excelxp options(sheet_name='Jungs') ;
proc print data=myclass noobs label ;
where sex='M' ;
run;

ods tagsets.excelxp close;
/*---------- bottom of sample code --------*/

Needless to say I'm very happy with it!

Kind regards
Robert Bardos

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
  • 3 replies
  • 1743 views
  • 0 likes
  • 4 in conversation