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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  I am not sure what you mean by more "readable" Excel tables. Using TAGSETS.EXCELXP, you can turn on AutoFilters quite easily and using STYLE overrides, you can implement font and color changes (not shown here).

cynthia

** the code;
    

ods tagsets.excelxp file="c:\temp\filter_table.xml" style=htmlblue

    options (doc='help' sheet_name="example" autofilter='all');
      

     proc print data=sashelp.shoes noobs;

  var region subsidiary product sales inventory returns;

  run;

 

ods _all_ close;

View solution in original post

5 REPLIES 5
Community_Help
SAS Employee

Hello, thank you for your message and being part of Communities on SAS. I noticed that you posted your message in our forum about the community itself - so I've moved it here to the https://communities.sas.com/community/support-communities/sas_macro_facility_data_step_and_sas_langu... Macro & Programming community so that it has better visibility. Thanks again!

ballardw
Super User

Tell us what the expected output should be. Not all of us do VBA. For example what does "autofilter" do?

cercig
Obsidian | Level 7

Hi @ballardw . Auto filter creates buttons on the top of your column,  so through that button you can filter unnecessary options on your excel table.

But it was only an example. i will use more vba options to create "more readable" excel tables.

Cynthia_sas
SAS Super FREQ

Hi:

  I am not sure what you mean by more "readable" Excel tables. Using TAGSETS.EXCELXP, you can turn on AutoFilters quite easily and using STYLE overrides, you can implement font and color changes (not shown here).

cynthia

** the code;
    

ods tagsets.excelxp file="c:\temp\filter_table.xml" style=htmlblue

    options (doc='help' sheet_name="example" autofilter='all');
      

     proc print data=sashelp.shoes noobs;

  var region subsidiary product sales inventory returns;

  run;

 

ods _all_ close;

cercig
Obsidian | Level 7

Thanks a lot @Cynthia@sas

It is good learn about ods options which I didn't know muc about it,

I tested the Auto Filter option, it works perfectly. Now I will try to find other formatting options in ODS, like merging cells or removing frames of cells or changing text color.

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
  • 3539 views
  • 3 likes
  • 4 in conversation