topic Re: How do I proc export to a macro activated Excel workbook in SAS Programming

How do I proc export to a macro activated Excel workbook

AugustV — Wed, 18 Jan 2017 12:54:34 GMT

I'm trying to export a dataset to a macro activated Excel workbook Code below

proc export

dbms=Excelcs

data=db_final

outfile="\\My_computer\KPM\Output\KPM_DK_Business.xlsm"

replace;

SHEET='Data1';

server="My_server";

port=9621;

run;

The workbook already exist, and so does sheet "Data1". I want to overwrite the existing "Data1" with the new export using "replace". But SAS creates a new sheet called "Data11" where it outputs the dataset.

How can I straighten this out?

(SAS version 9.4 and exporting to Excel 2010)

Appreciate all the help I can get!

Best

August

Re: How do I proc export to a macro activated Excel workbook

RW9 — Wed, 18 Jan 2017 13:39:59 GMT

There are many topics on here on this very subject. Its not easy, and depends on what version of SAS etc. Do a search. My advice, dump the data from SAS to CSV, then in your Excel file have a small bit of VBA to load that CSV and process in your Excel file.

Re: How do I proc export to a macro activated Excel workbook

rogerjdeangelis — Wed, 18 Jan 2017 13:56:35 GMT

SAS Forum: How do I proc export to a macro activated Excel workbook

https://goo.gl/CPxDyB
https://communities.sas.com/t5/General-SAS-Programming/How-do-I-proc-export-to-a-macro-activated-Excel-workbook/m-p/325652


HAVE EXISTING MACRO XLS NOTEOOK ANA WANT TO UPDATE AN EXISTING SHEET

Sheet CLASS

 +-------------------------+
 |      |    A      |   B  |
 +------+------------------+
 |      |           |      |
 |    1 |   NAME    |   AGE|
 |    2 |   Alfred  |   14 |
 |    3 |   Alice   |   13 |
 |    4 |   Barbara |   13 |
 |    5 |   Carol   |   14 |
 |    6 |   Henry   |   14 |
 +------------------+------+

CLASS


MANUALLY PLACE MACROS ARE IN THE WORKBOOK

/* T000225 CONVERTING ALL EXCEL CELLS TO CHARACTER  */
    Latest VBA macros Addticks - forces all excel cells to be character
    before importing excel sheets to SAS this macro should be used

   /* this will stop when cell has TIUQ which is quit backwards */
   /* this is probably the best 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

/* T000226 REMOVES TICKS FROM EXCEL CELLS  */
    Sub RemoveTicks()
    For Each currentcell In Selection
        If currentcell.HasFormula = False Then
            'Verifies that procedure does not change the
            'cell with the active formula so that it contains
            'only the value.
            currentcell.Formula = currentcell.Value
        End If
    Next
    End Sub


WANT UPDATE MUTIPLY AGE BY 2
=================================


 +-------------------------+
 |      |    A      |   B  |
 +------+------------------+
 |      |           |      |
 |    1 |   NAME    |   AGE|
 |    2 |   Alfred  |   14 |
 |    3 |   Alice   |   13 |
 |    4 |   Barbara |   13 |
 |    5 |   Carol   |   14 |
 |    6 |   Henry   |   14 |
 +------------------+------+

 CLASS

 SOLUTION (WORKING CODE)

   modify xel.class(keep=name age);
   age=age*2;


FULL SOLUTION

* Create the workbook;

libname xel "d:/xls/class.xlsx";

data xel.class;
  set sashelp.class(keep=name age);
run;quit;

* close to save ;
libname xel clear;

* note xlsb;
libname xel excel "d:/xls/class.xlsb" scantext=no;

data xel.class;
   modify xel.class(keep=name age);
   age=age*2;
run;

libname xel clear;

Re: How do I proc export to a macro activated Excel workbook

rogerjdeangelis — Wed, 18 Jan 2017 14:11:34 GMT

Correction WANT SHOULD BE

 +-------------------------+
 |      |    A      |   B  |
 +------+------------------+
 |      |           |      |
 |    1 |   NAME    |   AGE|
 |    2 |   Alfred  |   28 |
 |    3 |   Alice   |   26 |
 |    4 |   Barbara |   26 |
 |    5 |   Carol   |   28 |
 |    6 |   Henry   |   28 |
 +------------------+------+