Writing vba code in sas

Question

I want to implement my VBA code into my SAS-code, so I can do the whole process with one run. My SAS code reads a big SAS table, does some transformations, and finally exportes to an Excel file (the code is below). I also wrote some VBA code in the Excel file (for example AutoFiltering for some variables, you can see the code below).

The table looks like this:

A B C Var1 Var2 Var3
--------------------
1 1 1 10 15 20
1 1 2 15 20 30
1 2 1 20 30 40
1 2 2 30 40 50
2 1 1 40 50 60
2 1 2 50 60 70
2 2 1 60 70 80
..............
..............

However, I want to implement my VBA code into my SAS-code, so I can do the whole process with one run. I know how to open and run an Excel file in SAS (the code is below), but I don't know how to implement a VBA code in my SAS.

If you wonder why I want to implement my Macro-code in my SAS, I will work with similar SAS-tables many times in the future, so it would be more practicle to keep the whole-code in one place.

I just realized that I can't export an table in SAS in macro-enabled Excel format, XLSM. I guess it is also a challange. Also, it is not so practicle to save a Macro-code from an Excel file, because it must be saved in Adds-in menu. So it would be much better to handle the whole process in one place, like inside the SAS editor.

THE code in SAS which exports the final table to an Excel file:

PROC EXPORT DATA=File1
        OUTFILE= "&server\&env\test1.xlsx" 
        DBMS=EXCEL REPLACE; 
   SHEET="sheet1"; 
RUN;

The VBA code example in the Excel file to create AutoFilter for variables in the Excel file:

Sub Macro1()
    Dim N As Long, r As Range
With Sheets("sheet1")
    N = .Cells(Rows.Count, "B").End(xlUp).Row
    ReDim ary(1 To N)
    For i = 1 To N
        ary(i) = .Cells(i, 1)
    Next i
End With

Range("A1:F20").AutoFilter
ActiveSheet.Range("$A$1:$F$20").AutoFilter Field:=1, Criteria1:=ary, Operator:=xlFilterValues
End Sub

The code in SAS to start and run an Excel file in SAS:

OPTIONS NOXWAIT NOXSYNC;
   DATA _NULL_;
   RC=SYSTEM('START EXCEL');
   RC=SLEEP(0.5);
RUN;
FILENAME CMDS DDE 'EXCEL|SYSTEM';
DATA _NULL_;
   FILE CMDS;
   PUT "[OPEN(""&server\&env\test1.XLS"")]";
   PUT '[RUN("Macro1")]';
   PUT '[SAVE.AS("&server\&env\FORMATTED_FILE.XLSM")';
   PUT "[QUIT()]";
RUN;
QUIT;

You're saying that you want to be able to create a new Excel file via DDE and then create a new VBA macro in it programatically via SAS? — Joe
Basically, Yes. I want that my SAS code will read a SAS-table, export the result to an Excel file (XLSM if possible) and my SAS-code will also run some VBA-code to make the Excel file more beautiful. and I want that all those processes will be with only one run in only one code... — user3714330
Is the excel macro you're running always the same? Would a method using an excel template work? I think that's the standard way to do this; you store the excel macro in a 'template' workbook that is static, and execute it from there (while writing to a different workbook, or you can write to the template workbook and then SAVE AS a different workbook name). — Joe
I was planning to use the VBA in SAS for same table variables, but with changing data in each excel file. I would do only changes in "macro variables" at the beginning of the SAS file with %let var1=&var. You mean saving the Excel Macro as a static template workbook, you mean the XLSB format? and do you mean that I just simply save my Macro-code in any tamplate, and I copy-paste it whenever I need it? — user3714330
If your example is autofilter I would argue that Tagsets provides a great mechanism for doing that already. Also, you can export to XLSM, I've done it may times. What version of SAS do you have? — Reeza

Joe Joe · Accepted Answer · 2015-06-26T15:02:22

The common way to do this is to use a template file. You have your template saved, which has the excel macro saved in it (and perhaps also has some of the formatting done to it; using DDE you don't have to start with a blank worksheet, after all).

You can either use DDE to populate the template workbook/worksheet, and then "Save As" another file, or you can use DDE to create a new workbook and worksheet, open the template workbook, run the macro, close the template. Which you do may depend on whether you want to distribute the macro along with your results.

This allows you to run everything without interacting with it in any way - you don't have to add a new macro to it or anything, since the template macro already exists. Everything can be done in one run this way.

This is shown for example in the paper Step-by-Step in Using SAS® DDE to Create an Excel Graph Based on N Observations from a SAS Data Set, as well as several other papers on the subject.

Writing vba code in sas

3 Answers