Important: Your perl installation may require some encoding files. This is because ENCODING variable output by the SASXML tagset has the value windows-1252 On my test system, the perl program initially errored, with the message "Couldn't open encmap windows-1252.enc:" showing in the SAS log I downloaded http://search.cpan.org/src/MSERGEANT/XML-Parser-2.34 /Parser/Encodings/windows-1252.enc into my /perl/site/lib/XML/Parser/Encodings folder and the error went away.
%macro sas2xls ( file= , fileref= , sheet1=, sheet2=, sheet3=, sheet4=, sheet5= , sheet6=, sheet7=, sheet8=, sheet9=, sheet10= , sheet11=, sheet12=, sheet13=, sheet14=, sheet15= , sheet16=, sheet17=, sheet18=, sheet19=, sheet20= , sheet21=, sheet22=, sheet23=, sheet24=, sheet25= , sheet26=, sheet27=, sheet28=, sheet29=, sheet30= , sheet31=, sheet32=, sheet33=, sheet34=, sheet35= , sheet36=, sheet37=, sheet38=, sheet39=, sheet40= , sheet41=, sheet42=, sheet43=, sheet44=, sheet45= , sheet46=, sheet47=, sheet48=, sheet49=, sheet50= , perl= perl -w , deleteXML = 1 ); %* %* sas2xls - Create an excel file containing one worksheet per SAS dataset %* Requires Perl and some perl modules. %* %* Richard A. DeVenezia %* 11/18/03 adapted from sas2xls %* 11/23/03 handle time, date and datetime values %* 11/25/03 add translate to xmlappend invocations %* %* Does this: %* Use XML library engine to write SAS data sets to %* an xml file. This file is read by a Perl program %* which creates a multi-sheet workbook from it. %* %* Does not: %* Handle formatting issues. We will have to wait for SAS to get %* off their duff and write an ODS EXCEL destination. %* %* Perl Host requirements: %* Perl 5.005 (or later) %* Spreadsheet::WriteExcel %* Parse::RecDescent %* File::Temp %* %* Assumptions: %* Perl and SAS are installed on same host %* %* Argument categories and special considerations: %* %*----- %* Excel output %* %* FILE=<host-path> or %* FILEREF=<SAS-fileref> %* %* If fileref _WEBOUT is passed, the proper content-type will be prepended %* to the generated excel file %* %*----- %* Worksheet names and content source %* %* Form1: SHEET<n>=[~][<SheetName>]:[<Heading>]:<Dataset>|<CatalogEntry> %* Form2: SHEET<n>=<Dataset>|<CatalogEntry> %* %* <n> can range from 1 to 50 %* %* Caveat: Due to the use of : as a specification delimiter, a SHEET<n> %* parameter should not have a : in it (such as in a data set %* where clause). Use a SQL or Data Step view to get around %* this restriction. %* %* Form 1: %* %* [~][<SheetName>]:[<Heading>]:<Dataset>|<CatalogEntry> %* %* ~ is optional %* %* A special formatting indicator %* If SheetName starts with tilde (~) the worksheet is formatted %* to hide grid lines and all cells are Courier New font %* Use this feature when transferring a PROCs text output to Excel. %* A PROCs text output can be captured to a catalog using Proc PRINTTO. %* %* <SheetName> is optional %* %* The worksheet name. %* If not present, the dataset label of <Dataset> will be used %* If <Dataset> is unlabelled, then <Dataset> will be used %* %* <Heading> is optional %* %* Heading controls what appears in Row 1 of the Excel worksheet. %* Heading may be: %* LABEL - Column label, name is used if unlabelled column %* NAME - Column name %* NONE - No column heading row %* Heading defaults to NAME. %* %* <Dataset> %* %* A SAS data set or view to be placed in a Excel worksheet. %* <Dataset> may contain any valid dataset options such as where=, %* keep= and drop=. %* %* <CatalogEntry> %* %* A SOURCE catalog entry. Use either three-level or four-level. %* If catalog entry is captured PROC text output the argument %* should use the ~ prefix for best results. %* %* Form 2: %* %* <Dataset>|<CatalogEntry> %* %* <Dataset> %* %* A SAS data set or view to be placed in a Excel worksheet. %* <Dataset> may contain any valid dataset options such as where=, %* keep= and drop=. %* The sheet will be named according to the dataset label. %* If <Dataset> is unlabelled, then <Dataset> will be used. %* There will be a header row of variable names. %* %* <CatalogEntry> %* %* A SOURCE catalog entry. Use either three-level or four-level name. %* The worksheet name is the memname.entry part of the resolved %* four-level name and will be ~ formatted. %* %*----- %* Perl executable %* %* PERL=<host-path> %* %*----- %* Example, see bottom comment %* %*----- %* Caveats: %* Extraneous tables (layout and contents) needed by the Perl program %* are placed in the XML file. If you try to use the XML generated %* by this macro in other XML processors, you will have some confusion. %* Some 'non-printable' characters in SAS character variables are %* translated to spaces. %*; %local NOTES SOURCE SOURCE2 MPRINT; %let NOTES = %sysfunc (getoption(NOTES)); %let SOURCE = %sysfunc (getoption(SOURCE)); %let SOURCE2 = %sysfunc (getoption(SOURCE2)); %let MPRINT = %sysfunc (getoption(MPRINT)); * options NONOTES NOSOURCE NOSOURCE2 NOMPRINT; %local THIS VERSION; %local RC DSID I N SHEET TYPE STYLE; %local PART1 PART2 PART3 PART4 DS WN HD; %local LIB MEM OBJ TYP; %*--------------------------------------------------------------------------- %*; %let THIS = sas2xls; %let VERSION = 2003.11.18; %*--------------------------------------------------------------------------- %* Check for xmlappend macro; %if (not %sysfunc(exist(WORK.SASMACR.XMLAPPEND.MACRO, CATALOG))) %then %do; %put ERROR: &THIS: The required macro XMLAPPEND was not found.; %goto EndMacro; %end; %*--------------------------------------------------------------------------- %* Check for perlpgm data; %if (not %sysfunc(exist(WORK.PERLPGM))) %then %do; %put ERROR: &THIS: The required data WORK.PERLPGM was not found.; %goto EndMacro; %end; %*--------------------------------------------------------------------------- %* Check for XLS destination; %if (%superq(FILEREF) eq ) and (%superq(FILE) eq ) %then %do; %put ERROR: &THIS: FILEREF or FILE must be specified; %goto EndMacro; %end; %if (%superq(FILEREF) ne ) and (%superq(FILE) ne ) %then %do; %put ERROR: &THIS: Specify FILEREF or FILE, not both; %goto EndMacro; %end; %*--------------------------------------------------------------------------- %* Make sure that FILEREF specified has been previously assigned; %if (%superq(FILEREF) ne ) %then %do; %let FILE= ; %if (%sysfunc (fileref(&FILEREF)) > 0) %then %put ERROR: &THIS: Fileref &FILEREF is not assigned; %end; %*--------------------------------------------------------------------------- %* Prepare to examine each parameter; %let I = 1; %let N = 0; %*--------------------------------------------------------------------------- %* Examine each value of each sheet<n> parameter; %do %while (&I <= 50); %if (%superq(SHEET&I) eq ) %then %goto NxtSheet; %* slip in a leading : so that a leading : %* is recognized as a blank; %let SHEET = :&&SHEET&I; %let SHEET = %sysfunc(tranwrd(&SHEET,::,: :)); %let SHEET = %sysfunc(tranwrd(&SHEET,::,: :)); %let PART1 = %scan (&SHEET,1,:); %let PART2 = %scan (&SHEET,2,:); %let PART3 = %scan (&SHEET,3,:); %if (%qsubstr (&PART1%str( ),1,1) eq %str(~)) %then %do; %let PART1 = %substr(&PART1%str( ),2); %let STYLE = MONO; %end; %else %let STYLE = NORMAL; %if (%superq(PART1) eq) and (%superq(PART2) eq) and (%superq(PART3) eq) %then %do; %put ERROR: &THIS: SHEET&I=&&SHEET&I can not be parsed.; %put ERROR: &THIS: Continuing to next sheet; %goto NxtSheet; %end; %if (%superq(PART2) ne ) and (%superq(PART3) eq ) %then %do; %put ERROR: &THIS: SHEET&I=&&SHEET&I can not be parsed.; %put ERROR: &THIS: Continuing to next sheet; %goto NxtSheet; %end; %if (%superq(PART2) eq ) and (%superq(PART3) eq ) %then %do; %let DS = &PART1; %let HD = NAME; %let WN =; %end; %else %do; %let WN = &PART1; %let HD = &PART2; %let DS = &PART3; %end; %let dsid = %sysfunc (open (&DS)); %if &dsid = 0 %then %do; %* not an openable dataset, check if its a catalog entry; %let LIB = %scan (&DS,1,.); %let MEM = %scan (&DS,2,.); %let OBJ = %scan (&DS,3,.); %let TYP = %scan (&DS,4,.); %if (%superq(OBJ) eq ) %then %let OBJ = ########; %if (%superq(TYP) eq ) %then %let TYP = OUTPUT; %let DS = &LIB..&MEM..&OBJ..&TYP; %if (not %sysfunc(exist(&DS, CATALOG))) %then %do; %put ERROR: &THIS: SHEET&I=&&SHEET&I does not appear to be; %put ERROR: &THIS: an existing dataset or a catalog entry.; %put ERROR: &THIS: Continuing to next sheet; %goto NxtSheet; %end; %let type = CATA; %if (%superq(WN) eq ) %then %let WN = &DS; %if (%superq(PART2) eq and %superq(PART3) eq ) %then %do; %let STYLE=MONO; %let HD = NONE; %end; %end; %else %do; %if (%superq(WN) eq ) %then %let WN = %sysfunc(attrc(&dsid,LIB)).%sysfunc(attrc(&dsid,MEM)); %let dsid = %sysfunc (close (&dsid)); %let type = DATA; %end; %if (%superq(HD) eq ) %then %let HD = NAME; %let HD = %upcase (&HD); %if %sysfunc(index(|LABEL|NAME|NONE|,|&HD.|)) = 0 %then %do; %put ERROR: &THIS: SHEET&I=&&SHEET&I has invalid header row type; %put ERROR: &THIS: Continuing to next sheet; %goto NxtSheet; %end; %let N = %eval (&N + 1); %* DS - Data Set, WN - Worksheet Name, HD - Header row type; %local DS&N WN&N HD&N TYPE&N STYLE&N; %let WN&N = &WN; %let HD&N = &HD; %let DS&N = &DS; %let TYPE&N = &TYPE; %let STYLE&N = &STYLE; %NxtSheet: %let I = %eval (&I+1); %end; %*--------------------------------------------------------------------------- %* Check if any sheets indicated ; %if (&N = 0) %then %do; %put WARNING: &THIS: No sheets were indicated.; %goto EndMacro; %end; %*--------------------------------------------------------------------------- %* Output the data as XML, a Perl program will read this XML and write an %* multisheet xls file based on the data %* Note: extraneous info (layout and contents) needed by the Perl program %* is placed in the XML file, and said file if used by other XML processors %* will result in confusion.; %local workpath dirsepp dirsep; %local random random2 random3; %local xmlfile pl_file xlsfile xmlfref; %local start finish; %local perlpgm perlrun; %local translate; %*--------------------------------------------------------------------------- %* directory separator; %let workpath = %sysfunc(pathname(WORK)); %let dirsepp = %sysfunc(indexc(&workpath,\/)); %let dirsep = %substr(&workpath, &dirsepp, 1); %*-----; %let random = %substr (%sysfunc (ranuni(0), 9.7), 3); %let random2 = %sysfunc(mod(%sysevalf(&random+1),1e7),z7.); %let random3 = %sysfunc(mod(%sysevalf(&random+2),1e7),z7.); %*let random = sas2xls; %*let random2 = sas2xlx; %*let random3 = sas2xly; %*-----; %let xmlfile = &workpath.&dirsep._&random..xml; %let pl_file = &workpath.&dirsep._&random..pl; %let xlsfile = &workpath.&dirsep._&random..xls; %if (%superq(file) ne ) %then %let xlsfile = &file; %*--------------------------------------------------------------------------- %* if any of these characters are present in an xml file, %* then Perl module XML::Simple will complain. %* the characters will be translated to spaces by the xmlappend macro. %*; %let translate = &translate.010204050607080B0C0E0F; %let translate = &translate.101112131415161718191A1B1C1D1E1F; %let translate = &translate.808182838485868788898A8B8C8D8E8F; %let translate = &translate.909192939495969798999A9B9C9D9E9F; %*----- create the xml file for the Perl program; %do i = 1 %to &N; %let start = 0; %let finish = 0; %if &i = 1 %then %let start=1; data _&random; data = %sysfunc(quote(&&DS&i%str( ))); sheet = %sysfunc(quote(&&WN&i%str( ))); header = %sysfunc(quote(&&HD&i%str( ))); type = %sysfunc(quote(&&TYPE&i%str( ))); style = %sysfunc(quote(&&STYLE&i%str( ))); run; %* some layout meta data for the Perl program; %xmlappend ( file=&xmlfile , data=_&random , out=_layout_&i , start=&start , finish=&finish ) %let start = 0; %let finish = 0; %* output the table or catalog entry as xml; %if &&type&i = DATA %then %do; proc contents noprint data=&&DS&i out=_&random(keep=name label varnum format); run; %xmlappend ( file=&xmlfile , data=_&random , out=_contents_&i , start=&start , finish=&finish , translate=&translate ) %if &i = &N %then %let finish=1; %xmlappend ( file=&xmlfile , data=&&DS&i , out=_data_&i , start=&start , finish=&finish , translate=&translate ) %end; %else %do; filename _&random catalog "&&DS&i"; data _&random; infile _&random; input; length line $1000; line = _infile_; run; filename _&random; proc contents noprint data=_&random out=_&random2(keep=name label varnum); run; %xmlappend ( file=&xmlfile , data=_&random2 , out=_contents_&i , start=&start , finish=&finish , translate=&translate ) %if &i = &N %then %let finish=1; %xmlappend ( file=&xmlfile , data=_&random , out=_data_&i , start=&start , finish=&finish , translate=&translate ) proc delete data=_&random2; run; %end; proc delete data=_&random; run; %end; %*----- write and run the Perl program; %let perlpgm = _&random; %let perlrun = _&random2; %let xmlfref = _&random3; filename &xmlfref "&xmlfile"; filename &perlpgm "&pl_file"; filename &perlrun PIPE "&PERL &pl_file &xmlfile &xlsfile"; data _null_; set perlpgm; file &perlpgm ; len = length(source); put source $varying. len; run; data _null_; infile &perlrun; input; put _infile_; run; %if &deleteXML %then %let rc = %sysfunc (fdelete (&xmlfref)); %let rc = %sysfunc (fdelete (&perlpgm)); filename &xmlfref; filename &perlpgm; filename &perlrun; %EndMacro: options &NOTES &SOURCE &SOURCE2 &MPRINT; %mend;
* Create data set that holds perl program (required) ;
data perlpgm; input; source = _infile_; cards4; use strict; use XML::Simple; use Spreadsheet::WriteExcel; use Spreadsheet::WriteExcel::Utility; use Data::Dumper; use Cwd; my $dir = cwd(); my $xmlfile = $ARGV[0]; my $xlsfile = $ARGV[1]; my $lib = XMLin($xmlfile,ForceArray=>[qr/^_(DATA|CONTENTS)_\d+$/]); # Note: empty tags, <tag></tag>, become an empty hash, {}, when read by XMLin # Thus, ref() tests are needed to determine this situation #print Dumper($lib); #exit; my $sheet_num = 1; my $workbook = Spreadsheet::WriteExcel->new($xlsfile); while ($lib->{"_DATA_$sheet_num"}) { my $layout = $lib->{"_LAYOUT_$sheet_num"}; my ($format, $tformat, $dformat, $dtformat); my $R = 0; my $C = 0; my ($i,$j); my $worksheet = $workbook->add_worksheet ($layout->{sheet}); if ($layout->{style} eq 'MONO') { $worksheet->hide_gridlines(2); $format = $workbook->add_format(font=>"Courier New"); $tformat = $workbook->add_format(font=>"Courier New"); $dformat = $workbook->add_format(font=>"Courier New"); $dtformat = $workbook->add_format(font=>"Courier New"); } else { $tformat = $workbook->add_format(); $dformat = $workbook->add_format(); $dtformat = $workbook->add_format(); } $tformat->set_num_format('h:mm:ss'); $dformat->set_num_format('dd-mmm-yyyy'); $dtformat->set_num_format('dd-mmm-yyyy hh:mm:ss'); my $vars = $lib->{"_CONTENTS_$sheet_num"}; my ($varname, $varnum); my @columns; for ($i=0;$i<@$vars;$i++) { $varname = $vars->[$i]->{NAME}; $varnum = $vars->[$i]->{VARNUM}; $columns[$varnum] = $i; } # output header row of requested type if ($layout->{header} eq 'NAME') { for ($i=1;$i<@columns;$i++) { $worksheet->write($R,$C++,$vars->[$columns[$i]]->{NAME}, $format); } $worksheet->freeze_panes(1,0); } elsif ($layout->{header} eq 'LABEL') { for ($i=1;$i<@columns;$i++) { my $value = !ref($vars->[$columns[$i]]->{LABEL}) ? $vars->[$columns[$i]]->{LABEL} : $vars->[$columns[$i]]->{NAME} ; $worksheet->write($R,$C++,$value,$format); } $worksheet->freeze_panes(1,0); } my $data = $lib->{"_DATA_$sheet_num"}; for ($i=0;$i<@$data;$i++) { $C=0; $R++; for ($j=1;$j<@columns;$j++) { my $colname = $vars->[$columns[$j]]->{NAME}; my $colvalue = !ref($data->[$i]->{$colname}) ? $data->[$i]->{$colname} : '' ; $_ = $colvalue; if ( /^(\d\d):(\d\d):(\d\d)(\.\d*)$/ ) { $colvalue = (($1*60 + $2)*60 + $3 + $4) / 86400 ; $worksheet->write($R,$C++,$colvalue,$tformat); $worksheet->set_column ($C-1,$C-1,7.5); } elsif ( /^(\d\d\d\d)-(\d\d)-(\d\d)$/ ) { $colvalue = xl_date_list($1,$2,$3); $worksheet->write($R,$C++,$colvalue,$dformat); $worksheet->set_column ($C-1,$C-1,11); } elsif ( /^(\d\d\d\d)-(\d\d)-(\d\d)T(\d\d):(\d\d):(\d\d)(\.\d*)$/ ) { $colvalue = xl_date_list($1,$2,$3,$4,$5,$6+$7); $worksheet->write($R,$C++,$colvalue,$dtformat); $worksheet->set_column ($C-1,$C-1,18.5); } else { $worksheet->write($R,$C++,$colvalue,$format); } } } $sheet_num++; } $workbook->close(); ;;;; run;
Sample code
*; /*; %include "\\extreme\macros\xmlib.sas"; options nomprint; %xmlib; options mprint notes; filename pgm catalog 'work.test.rptprog.source'; data _null_; input; file pgm; put _infile_; cards4; filename output catalog 'work.text.report.output'; proc printto print=output new; run; options nocenter nodate nonumber; title; footnote; proc print data=sashelp.class; run; proc printto print=print; run; filename output; ;;;; run; %include pgm; filename pgm; options mprint; data foo; attrib date format=date9. label='Todays Date' dt format=datetime16. x length=8 format=8.4 y length=$200 label='Why is the question' ; do x = 0 to 255; y = put(x,3.)||byte(x)||'ABC'; output; end; stop; run; proc sql; create table table as select datepart (crdate) as cr_date format=date9. , timepart (crdate) as cr_time format=time8. , * from dictionary.tables; quit; %sas2xls ( FILE=c:\temp\_WEBOUT.xls , SHEET1=SASHELP.CLASS , SHEET2=Libraries::SASHELP.VSLIB , SHEET3=A list of the A tables:Label:SASHELP.VSTABLE (WHERE=(MEMNAME LIKE 'A%')) , SHEET4=work.test.rptprog.source , SHEET5=work.text.report , SHEET6=:Label:WORK.FOO , SHEET7=:label:TABLE , deleteXML = 0 ); options noxwait noxsync; x "c:\temp\_WEBOUT.xls"; options xwait xsync; */;