topic Re: how to export table to specific cell range of define sheet of excell in SAS Enterprise Guide

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

akarki001 — Thu, 30 Sep 2021 14:34:56 GMT

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.

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

blueskyxyz — Fri, 01 Oct 2021 01:23:25 GMT

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-define-sheet-of/m-p/771388

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

akarki001 — Fri, 01 Oct 2021 12:48:01 GMT

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.

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

blueskyxyz — Sat, 02 Oct 2021 10:05:05 GMT

@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

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

akarki001 — Mon, 04 Oct 2021 17:37:12 GMT

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.

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

blueskyxyz — Thu, 07 Oct 2021 14:24:34 GMT

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;

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

akarki001 — Thu, 07 Oct 2021 15:06:31 GMT

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.

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

SASKiwi — Thu, 07 Oct 2021 23:45:31 GMT

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.

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

blueskyxyz — Fri, 08 Oct 2021 02:30:22 GMT

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

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

AlanC — Fri, 08 Oct 2021 02:46:29 GMT

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

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

akarki001 — Fri, 08 Oct 2021 14:00:15 GMT

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.

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

blueskyxyz — Sat, 09 Oct 2021 02:59:02 GMT

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