Date:         Tue, 3 Nov 2009 09:18:28 -0500
Reply-To:     Proc Me <procme@CONCEPT-DELIVERY.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Proc Me <procme@CONCEPT-DELIVERY.COM>
Subject:      Re: comment on recent sample 35574 at SAS

>I don't now if this will be acceptable, but it does operate without
>SAS/ACCESS to PC file formats.  It does require EXCEL.  It might be an
>acceptable method if you want to do extensive formatting of the sheet.
> I included some limited formatting "auto fit" to illustrate.   I left
>out all the SAVE and naming part but that depends on how you want it
>to work.
>
>This is an example of "do all tasks in one data step".   While fairly
>complicated it demonstrates some interesting SAS data step features.

Looks good to me: I like it, and I've learnt a lot from it, thank you!

As an example of how this can be extended, I've added some code to treat
dates differently: if a variable is formatted as date9. then the code
takes the serial date, adds 21916 (the number of days between 1 Jan 1920
and 1 Jan 1960) and then generates code to format the columns as dates in
Excel. This could of course be extended to recognise a range of date
formats, etc.

data _null_;
   if 0 then set &data;
   length _name_ $32 _type_ $1 _length_ _i_ 8 _label_ _script_ _filevar_
_command_ $256 _value_ $32767 _format_ $256 _vars_ 8;
   /* if var n is a date this will hold a 1, else 0 */
   array _datecols_(256) 3 _temporary_;
   /* Get number of variables */
   _name_ = ' ';

   do _i_ = 1 by 1;
      call vnext(_name_);
      if _name_ eq '_name_' then leave;
      /* Populate date column array */
      if vformatx(_name_) = 'DATE9.' then _datecols_(_i_) = 1;
      else _datecols_(_i_) = 0;
      end;
   /* Store number of variables */
   _vars_ = _i_ - 1;

   _script_   = catx('\',pathname('WORK'),'SAS2EXCEL.vbs');
   _filevar_  = _script_;

   /* write the script */
   file dummy1 filevar=_filevar_;

   put 'Set objExcel = CreateObject("Excel.Application")';
   put 'With objExcel';
   put +3 '.Visible = True';
   put +3 '.DisplayAlerts = False';
   put +3 '.Workbooks.Add';
   put +3 'Set objSheet = .ActiveWorkbook.Worksheets(1)';
   do _i_ = 1 by 1;
      call vnext(_name_,_type_,_length_);
      if _name_ eq '_name_' then leave;
      _label_ = vlabelX(_name_);
      if _name_ eq _label_
         then put +3 'objSheet.Cells(1,' _i_ ').value=' _name_:$quote34.;
         else put +3 'objSheet.Cells(1,' _i_ ').value='
_name_:$quote34. '& vblf & vblf & ' _label_:$quote256.;
      end;

   do _n_ = 2 by 1 until(_eof_);
      set &data end=_eof_;
      _name_ = ' ';
      do _i_ = 1 by 1;
         call vnext(_name_);
         if _name_ eq '_name_' then leave;
         _value_ = vvalueX(_name_);
         /* get the format name */
         _format_ = vformatx(_name_);
         /* if the format is date9 then convert date to serial */
         /* number and 1960 SAS to 1920 Excel*/
         if _format_ = 'DATE9.' then
            _value_ = put(inputn(_value_, _format_) + 21916,
8.);
         put +3 'objSheet.Cells(' _n_ ',' _i_ ').value='
_value_ :$quote32767.;
         end;
      end;
   /* Format date vars as dates */
   do _i_ = 1 to _vars_;
      if _datecols_(_i_) then
         put +3 'objSheet.Cells(1, ' _i_ ').EntireColumn.NumberFormat
= "dd/mm/yyyy"';
      end;

   put +3 'Set objRange = objSheet.UsedRange';
   put +3 'objRange.columns.autofit';
   put +3 'End With';


   /* close the script file by opening another file*/
   _filevar_ = catx('\',pathname('WORK'),'DUMMY.vbs');
   file dummy1 filevar=_filevar_;

   /* look at the script, not necessary but may be useful */
   infile dummy2 filevar=_script_ end=eof;
   do _n_ = 1 by 1 while(not eof);
      input;
      putlog _n_ z4. + 1 _infile_;
      end;

   /* call the script */
   _command_ = catx(' ','cscript',quote(strip(_script_)));
   infile dummy3 pipe filevar=_command_ end=eof truncover;
   do while(not eof);
      input;
      putlog _infile_;
      end;
   stop;

run;