topic Re: Export dataset to a specified range of cell of an existing excel spreadsheet in SAS Programming

Export dataset to a specified range of cell of an existing excel spreadsheet

akarki001 — Thu, 30 Sep 2021 20:48:37 GMT

I am trying to find the solution to export dataset to a specified range of cell of an existing excel spreadsheet? Please help me if there is any option in proc export or other ods?

Re: Export dataset to a specified range of cell of an existing excel spreadsheet

SASKiwi — Thu, 30 Sep 2021 21:03:04 GMT

PROC EXPORT supports this but only if you use DBMS EXCEL or EXCELCS.

Re: Export dataset to a specified range of cell of an existing excel spreadsheet

akarki001 — Fri, 01 Oct 2021 12:43:50 GMT

Hi SASKiwi,
Thank you so much. would you mind to explain little bit more how i can i add those in following code please:
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: Export dataset to a specified range of cell of an existing excel spreadsheet

Sajid01 — Fri, 01 Oct 2021 14:43:33 GMT

Hello @akarki001
Please have a look at the following.
https://communities.sas.com/t5/General-SAS-Programming/Exporting-SAS-dataset-into-specific-cells/td-p/401663

https://stats.idre.ucla.edu/sas/faq/how-can-i-send-sas-dataresults-to-specific-cells-in-an-excel-spreadsheet/

Re: Export dataset to a specified range of cell of an existing excel spreadsheet

akarki001 — Fri, 01 Oct 2021 16:52:45 GMT

Hi Sajid01,
I have gone through it earlier. Would you mind to elaborate how does it works please. Thank you.

Re: Export dataset to a specified range of cell of an existing excel spreadsheet

Sajid01 — Sat, 02 Oct 2021 00:02:50 GMT

The basic requirement is that you should have SAS and Excel installed on the same machine and of course WIndows OS.
Though SAS these days does not recommend it, this is the only direct option you have as I know of. The following SAS reference has good examples. Do read this, if you have not read already done so. (https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/hostwin/p0rbba3syo5gf1n1gf17jd7zzhwq.htm)
This another good reference ( https://www.lexjansen.com/nesug/nesug05/pos/pos15.pdf ) .

The instructions are pretty clear.
In case you have an error please post the logs.