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 |
+------------------+------+
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.