how to export table to specific cell range of define sheet of excell

[akarki001]

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.

[blueskyxyz]

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;

https://communities.sas.com/t5/SAS-Enterprise-Guide/how-to-export-table-to-specific-cell-range-of-de...

[akarki001]

PROC EXPORT DATA=DATA
outfile="C:\test.xlsx"
DBMS=xlsx replace label;
sheet=sheet1;
run;
I need to populate my output C6:V10 only output numbers keeping heading fix and rows are fixed. I mean I have 6 by 20 matrix in existing sheet, just want to fix 1st row (heading) and 1st column (Name) fixed. Thank you so much for your help.

[blueskyxyz]

@akarki001, could you upload the sample dataset and the result you want? or you can debug the code above with new options to get what you want

[akarki001]

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;
I have State and Month is fixed in the middle of excel sheet "State". I need to only export numbers not month and state in state sheet. Keep in mind i have many other sheet in the excel file. Thank you so much for your help.

[blueskyxyz]

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;

[akarki001]

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.

[SASKiwi]

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.

[blueskyxyz]

as saskiwi mentioned, maybe you can get the final result with two steps:
1. export the sas datasets in the excel using proc export or ods excel
2. use VBA , python, java ..., put the range of step 1 on your template

[AlanC]

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

[akarki001]

Thank you so much for your help and concerns. At this time i am looking for the solution only in SAS. I don't think i have any option at this point. Just populate SAS output in assigned cells with out deleting or overwriting in existing file and sheet because i have many sheets in the file and also other stuffs in the sheet itself. Once again thank you so much for your help and concerns. Thank you.

[blueskyxyz]

AlanC, thanks for sharing your material about C#, I will try to learn the new thing