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
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.
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;
Correction WANT SHOULD BE
+-------------------------+
| | A | B |
+------+------------------+
| | | |
| 1 | NAME | AGE|
| 2 | Alfred | 28 |
| 3 | Alice | 26 |
| 4 | Barbara | 26 |
| 5 | Carol | 28 |
| 6 | Henry | 28 |
+------------------+------+
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.