BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Unito
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Mokben
Fluorite | Level 6

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" ) ;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Super User

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
Barite | Level 11
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
Fluorite | Level 6

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" ) ;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 14795 views
  • 3 likes
  • 6 in conversation