BookmarkSubscribeRSS Feed
Weezy
Calcite | Level 5

Hello everyone 

 

Can I export SAS output to excel file xlsm ??

(I work wtih SAS entreprise guide 7.1 32bits)

 

thanks

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

Weezy
Calcite | Level 5

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)

Weezy
Calcite | Level 5

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)

Kurt_Bremser
Super User

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.

rogerjdeangelis
Barite | Level 11
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;

Weezy
Calcite | Level 5

Thanks !! 🙂 @rogerjdeangelis

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 6030 views
  • 1 like
  • 4 in conversation