export large datasets to .xlsm file

[Unito]

Dear Sas folks,

I try to export a large table (40k records comparable to sashelp.snacks) to an existing .xlsm file (microsoft excel macro file). But i dont's succeed 😞

Does anyone have the golden tip?

i tried an failed with the following methods:

1) use proc export with dbms=xlsx. SAS creates a xlsx file even if the outfile is defined als xlsm.

2) use proc export with dbms=xlsm i get the message  errror: DBMS type XLSM not valid for export.

3) assign a libname with the pc engine to the macro file and  use ods excel and proc print

4) assign a libname with the pc engine to the macro file and use a range in the xlsm file. then use proc copy. success with small files, no succes with large files.

5)  assign a libname with the pc engine to the macro file and then use a data stap

i use sas EG 6.1. and SAS version 9.4.

Kind regards,

Unito

[RW9]

SAS does not interact with XLSM.  From what I remember XLSM files contain binary components for the VBA to work, hence they are not really open source.

If you are happy using Excel and VBA, then the simplest way to acheive what you want is to export the data from SAS to CSV, then in your XLSM write a small bit of VBA code (doesn't actually have to stay in the code, you could do it in a separate XLSM), which opens the CSV, and processes the data into your XLSM file.

Pretty simple, create an Excel.  Create an onpen VBA which opens the XLSM file, then opens the CSV file, then copies the data from A2:xlEnd  to your XLSM where you want to put it.

[Kurt_Bremser]

To extend @RW9: Using .xlsm instead of just .xlsx suggests additional logic is kept there. Frankly, I would never use Excel for more than data display, and in fact I don't even do that. Keep the logic in SAS, where it is much easier to document and keep a version history of SAS codes.

[Reeza]

It's your SAS version that matters not EG. 

You can use the following to find your version, it will display in the log.  

%put &sysver;

if if you're on SAS 9.2 you may have issues. 

SAS 9.3 should have options. Worse case scenario look up a macro called rich/poor proc expert that will allow you export your data. 

[rogerjdeangelis]

SAS Forum: export large datasets to .xlsm

Cross posted from SAS-L
You can find a lot of information on dealing with foreign files on SAS-L

http://goo.gl/9OzDmv
https://communities.sas.com/t5/Base-SAS-Programming/export-large-datasets-to-xlsm-file/m-p/294316

HAVE (XLSM formatted Excel file)

1
2
3


SHEET1

WANT (Append 4)

HAVE (XLSM formatted Excel file)

1
2
3
4

SHEET1

SOLUTION

* It might be better to write to another xlsm workbook or
  to write to a temp XLSM and rename it. Unless your code is rock solid
  it is easy to corrupt your original XLSM;

%utl_submit_py64(%nrbquote(
from openpyxl import Workbook;
from openpyxl import load_workbook;
wb = load_workbook(filename='d:/xls/utl_xlsm_sample.xlsm', read_only=False, keep_vba=True);
/* grab the active worksheet */
ws = wb.active;

/* Data can be assigned directly to cells */
ws['A4'] = 88;

wb.save('d:/xls/utl_xlsm_sample.xlsm');
));

SAS MACRO


%macro utl_submit_py64(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 $255;
    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;
  %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;

[Mokben · Accepted solution]

this will work : 

%sysexec( copy "Path\workbookname.xlsm"

                          "Path\workbookname.xlsx" ) ;

Proc export data = your_data outfile = "Path\workbookname.xlsx"

                    Dbms = Xlsx replace ; 

run ; 

%sysexec( copy "Path\workbookname.xlsx"

                          "Path\workbookname.xlsm" ) ;