Can we export and paste sas output table in define cell of excel sheet? e.g. i need to export sas results to cell B10 to V40 of sheet 1. Thank you.
ods excel file="D:\temp.xlsx" options(
start_at="2,8" /*starting point for B cell*/
frozen_headers="10" /*B10,10=8+2, frozen_headers=frozen_rowheaders+row_repeat*/
frozen_rowheaders="8" /*frozen_row for blank row*/
autofilter="1-5"
sheet_name="Sales Report"
row_repeat="2" /*row for head*/
embedded_titles="yes");
proc print data=sashelp.orsales;
title "use proc print";
run;
ods excel close;
data new;
input State $ Jan $ Feb $ Mar $ Apr $;
datalines;
CA 100 200 300 400
NC 200 100 400 300
SC 400 300 200 100
VA 300 400 100 200
;
run;
/*starting point for C cell*/
ods excel file="D:\new.xlsx" options(
start_at="3,6" /*6=2+4*/
frozen_headers="6" /*6=2+4*/
frozen_rowheaders="3" /*row=6,col=3*/
autofilter="1-5"
sheet_name="State"
row_repeat="2" /*6=2+4*/
embedded_titles="no");
/*proc report data=new nowd noheader;*/
/* column State Jan Feb Mar Apr;*/
/*run;*/
/**/
proc print data=new noobs;
run;
ods excel close;
Dear Pyrite
Thank you so much for the code. My problem is I have exciting file with multiple sheet. I just want to add numbers within templet without hurting other sheet. ods excel will removed all sheet and rewrite but i don't want to touch other sheet. I just want to just populate output in templet. Templet has some heading and text which should be as it is. On above example: I want populate 4x4 matrix number in C6 to F9 without touching any other text. I have heading and some definition in row 1 to row4, B5 has column heading and i want fix (un-touch) row 5 and column B5. Again i don't want overwrite, just want to populate C6 to C9 by numbers without touching other sheets and text within sheet. I have 100 sheets in the file. Hope you understand. Once again thank you so much for your help.
What you want to do is not possible using ODS EXCEL as you have to build a complete workbook from scratch every time.
It also isn't possible with PROC EXPORT which allows you to completely replace one sheet and leaves others untouched.
The best solution I can think of is to use PROC EXPORT to update a "standard sheet" and then have an Excel macro or formula to copy the data to its required sheet and cells.
I work with Excel and SAS dataset at every client engagement and almost daily.
I never use SAS to export to Excel. SAS datasets are ODBC and OleDb compliant. Read the data into an Excel technology and do everything in alternate code. You can use C#/VSTO, or C#/EPPlus, C#/Excel interop, C#/GemBox (cost $). I would recommend not using VBA, Python, or Java. Use MS tech to work with MS tech. There are loads of examples out there. You can download mine, for free, here: GitHub - savian-net/ExcelSasDemo
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.