Date:         Wed, 4 Nov 2009 15:58:09 -0500
Reply-To:     Chang Chung <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Chang Chung <chang_y_chung@HOTMAIL.COM>
Subject:      Re: comment on recent sample 35574 at SAS
Comments: To: "Data _Null_;" <iebupdte@GMAIL.COM>

On Mon, 2 Nov 2009 11:58:33 -0600, Data _null_; <iebupdte@GMAIL.COM> wrote:
...
>I don't now if this will be acceptable, but it does operate without
>SAS/ACCESS to PC file formats.  It does require EXCEL.
...
Hi, _null_,

Thanks for sharing a piece of excellent code. I would add that in order to
run, it also requires Windows Script Host enabled.

In terms of coding styles, it can be made clearer by separating (as much as
we can) the window scripting vbs code from the sas data step code. Here is
my try using a technique of expanding placeholders. I also love to clean up
as much as I can. This ran on sas 9.2 on windows vista with excel 2007
installed locally.

Cheers,
Chang


/* exporting dataset to an excel sheet
   via WScript (vbs). Slow but flexible.
   Modified from data _null_^s SAS-L
   posting archived at UGA: http://tinyurl.com/yl9bvxb or
   http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475
   chang_y_chung@hotmail.com 20081104
*/

%let data = sashelp.shoes;
%let pwd = %sysfunc(pathname(WORK));
%let vbs = &pwd\sas2exl.vbs;
%let xls = &pwd\sas2exl.xls;

/* get the variable names and labels */
proc contents data=&data out=contents(keep=name label
   rename=(name=_name label=_label)) noprint;
run;

/* write a WSH script in vbs.
   substituting placeholders with data, if any */
data _null_;
   infile cards;
   file "&vbs";
   length _inLine _outLine _value $32767;
   input;
   _inLine = _infile_;

/* line without place holders */
   if index(_inLine, "#row#") = 0 then do;
      _outLine = _inLine;
      link putLine;
      return;
   end;

/* header row */
   do _col = 1 by 1 while (not _headerDone);
      set contents end = _headerDone;
      _row = 1;
      _value = quote(trim(_name));
      if not missing(_label) then do;
         _newLine = " & vbLF & vbLF & ";
         _label = quote(trim(_label));
         _value = catx(_newLine, _value, _label);
      end;
      _valueQuote = 0;
      link expand;
      link putLine;
   end;
   _nCols = _col - 1; /* we get to know the n columns */

/* other rows */
   do _row = 2 by 1 while (not _bodyDone);
      set &data end = _bodyDone;
      do _col = 1 to _nCols;
         set contents point=_col;
         _value = vvaluex(_name);
         _valueQuote = 1;
         link expand;
         link putLine;
      end;
   end;

return; /* end of main */

/* utilities */
   expand:
      _value = trim(_value);
      if _valueQuote then _value = quote(trim(_value));
      _outLine = trim(tranwrd(tranwrd(tranwrd(_inLine,
         "#row#", strip(put(_row, best.))),
         "#col#", strip(put(_col, best.))),
         "#value#", _value)
      );
   return;

   putLine:
      _outLine = trim(_outLine);
      _lineLength = length(_outLine);
      put _outLine $varying. _lineLength;
   return;

/* vbs file template */
cards;
' sas2excel.vbs -- automatically generated

' open a sheet
Set app = CreateObject("Excel.Application")
app.Visible = True
app.DisplayAlerts = False
app.Workbooks.Add

' write data out
Set sheet = app.ActiveWorkbook.Worksheets(1)
sheet.Cells(#row#, #col#).Value = #value#

' a little bit of formatting
sheet.UsedRange.columns.autofit

' save the excel sheet and clean up
app.DisplayAlerts = False ' to overwrite
app.ActiveWorkbook.SaveAs(WScript.Arguments(0))
app.Quit
set sheet = nothing
set app = nothing
;
run;

/* call the window script -- this should open excel */
filename cscript pipe "cscript ""&vbs"" ""&xls""";
   data _null_;
      infile cscript end=end;
      do while(not end);
         input;
         put _infile_;
      end;
      stop;
   run;
filename cscript clear;