Desktop productivity for business analysts and programmers

Export to excel xlsm

Reply
New Contributor
Posts: 4

Export to excel xlsm

Hello everyone 

 

Can I export SAS output to excel file xlsm ??

(I work wtih SAS entreprise guide 7.1 32bits)

 

thanks

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Export to excel xlsm

XLSM is Open Document format file with binary macro component.  You can't write this file out from SAS as the binary macro module is proprietary (which is one reason why OO format is a bit of a joke).  Why do you need to export to that specific file format, why not XLSX.  Is it because you have a template file and you want to add data in?  If so dump your SAS data to CSV and use VBA to read that into the document you already have (and you should know how to do this as you are already using VBA in your macro enabled XLSM).

New Contributor
Posts: 4

Re: Export to excel xlsm

I need to export to that specific file format because my file excel contains an sheet with a board and I have to fill this table by making calculations with output SAS. These calculations are realized with macro (VBA).

 

See attechement for exemple. 

(Sorry If my english it's not correct)

New Contributor
Posts: 4

Re: Export to excel xlsm

I need to export to that specific file format because my file excel contains an sheet with a board and I have to fill this table by making calculations with output SAS. These calculations are realized with macro (VBA).

 

See attechement for exemple. 

(Sorry If my english it's not correct)

Esteemed Advisor
Posts: 6,661

Re: Export to excel xlsm

Apart from @RW9's suggestion to use CSV as file format and import into Excel with VBA, you could use the MS Office Add-in to retrieve data from SAS into Excel.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: Export to excel xlsm

SAS/Python: Updating excel macro enabled workbooks

I don't have SAS  32 or 16bit windows available.

Win 7 64bit
Excel 2010 64bit
SAS 64bit
Pythin 2.7 - openpyxl 2.4

I think only Python can do this? Not SAS, R or Perl?
Recent WPS has an interface to Python can do this.  I don't have it.


HAVE macro enabled excel 2010 64bit  workbook d:/xls/classcop.xlsm
==================================================================

Only one empty sheet in

  +----------------------------------------------------------------+
  |     A      |     B      |    C       |    C       |   ...      |
  -----------------------------------------------------------------+
1 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+
2 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
. |   ...      |   ...      |   ...      |   ...      |    ...     |
  +------------+------------+------------+------------+------------+

* manually add this VBA macro;
/*
Sub AddTicks()
  Dim LastPlace, Z As Variant, X As Variant
  LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
  ActiveSheet.Range(Cells(1, 1), LastPlace).Select
  Z = Selection.Address   'Get the address
      For Each X In ActiveSheet.Range(Z)  'Do while
          If Len(X) > 0 Then      'Find cells with something
              X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X))  '39 is code for tick
          Else
              X.FormulaR1C1 = ""  'If empty do not put tick
          End If
          If X = "TIUQ" Then
              Exit Sub
          End If
      Next
End Sub
*/


WANT  update sheet class with sashelp.class
===========================================

  +----------------------------------------------------------------+
  |     A      |    B       |     C      |    D       |    E       |
  +----------------------------------------------------------------+
1 | NAME       |   SEX      |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+------------+
2 | ALFRED     |    M       |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+------------+
N | WILLIAM    |    M       |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+------------+

[class]

WORKING CODE (note keep_vba option)
====================================

    wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True);

    libname sd1 "d:/sd1";
    call missing(of _all_);
    output xel.class;

    manually add a VBA macro and save as macro enabled d:/xls/class.xlsm


FULL SOLUTION
==============

* delete dataset;
proc datsets lib=sd1;
delete class;
run;quit;

* delete spreadsheets;
* create dataset and xlsx file;
%let fyl=d:/xls/class.xlsx;
%utlfkil(&fyl); * delete if exist;

* create empty workbook and SAS data for update;
libname xel "&fyl";
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.class  xel.class;
  set sashelp.class;
  output sd1.class;
  call missing(of _all_);
  output xel.class;
run;quit;
proc sql;
  drop table xel.class
;quit;
libname xel clear;

/*
Up to 40 obs from sd1.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5

d:/xls/class.xlsx

  +----------------------------------------------------------------+
  |     A      |     B      |    C       |    C       |   ...      |
  -----------------------------------------------------------------+
1 |            |            |            |            |            |
  +------------+------------+------------+------------+------------+

  [CLASS]
*/

* manually add a VBA macro and save as macro enabled d:/xls/class.xlsm;
/*
Sub AddTicks()
  Dim LastPlace, Z As Variant, X As Variant
  LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
  ActiveSheet.Range(Cells(1, 1), LastPlace).Select
  Z = Selection.Address   'Get the address
      For Each X In ActiveSheet.Range(Z)  'Do while
          If Len(X) > 0 Then      'Find cells with something
              X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X))  '39 is code for tick
          Else
              X.FormulaR1C1 = ""  'If empty do not put tick
          End If
          If X = "TIUQ" Then
              Exit Sub
          End If
      Next
End Sub
*/


* copy the macro enabled workbook and update the copy(backup VBA code);
%bincop(in=d:/xls/class.xlsm,out=d:/xls/classcop.xlsm);

/*
You can do this by hand
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file "d:/xls/classcop.xlsm" is:
      Filename=d:\xls\classcop.xlsm,
*/

*            _   _
 _ __  _   _| |_| |__   ___  _ __
| '_ \| | | | __| '_ \ / _ \| '_ \
| |_) | |_| | |_| | | | (_) | | | |
| .__/ \__, |\__|_| |_|\___/|_| |_|
|_|    |___/
;

%utl_submit_py64old("
from openpyxl.utils.dataframe import dataframe_to_rows;
from openpyxl import Workbook;
from openpyxl import load_workbook;
from sas7bdat import SAS7BDAT;
with SAS7BDAT('d:/sd1/class.sas7bdat') as m:;
.   clas = m.to_data_frame();
print(clas);
wb = load_workbook(filename='d:/xls/classcop.xlsm', read_only=False, keep_vba=True);
ws = wb.get_sheet_by_name('class');
rows = dataframe_to_rows(clas);
for r_idx, row in enumerate(rows, 1):;
.   for c_idx, value in enumerate(row, 1):;
.        ws.cell(row=r_idx, column=c_idx, value=value);
wb.save('d:/xls/classcop.xlsm');
");

*
 _ __ ___   __ _  ___ _ __ ___  ___
| '_ ` _ \ / _` |/ __| '__/ _ \/ __|
| | | | | | (_| | (__| | | (_) \__ \
|_| |_| |_|\__,_|\___|_|  \___/|___/

;

* macros;

%macro utl_submit_py64old(pgm)/des="Semi colon separated set of py commands";
  * write the program to a temporary file;
  filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
  data _null_;
    length pgm  $32755 cmd $1024;
    file py_pgm ;
    pgm=&pgm;
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'));
        if cmd=:'.' then cmd=substr(cmd,2);
        put cmd $char96.;
        putlog cmd $char96.;
      end;
  run;

  run;quit;
  %let _loc=%sysfunc(pathname(py_pgm));
  %put &_loc;
  filename rut pipe  "C:\Python_27_64bit/python.exe &_loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename py_pgm clear;
%mend utl_submit_py64;


%macro BinCop(
   in=\\filesrv04\stat\peggcsf\sd01\meta\datamart\bonepain_2008\docs\rogerdeangelis\bne.xls
   ,out=c:\pis\bne.xls
  ) / des="Copy a binary file";
data _null_;
  infile "&in" recfm=n;
  file "&out" recfm=n;
  input byt $char1. @@;
  put byt $char1. @@;
run;
%mend BinCop;

New Contributor
Posts: 4

Re: Export to excel xlsm

Thanks !! Smiley Happy @rogerjdeangelis

Ask a Question
Discussion stats
  • 6 replies
  • 264 views
  • 1 like
  • 4 in conversation