topic Re: proc export a dataset into certain cells of excel file in SAS Programming

proc export a dataset into certain cells of excel file

ForrestYao — Tue, 13 Aug 2019 15:12:23 GMT

Hi there,

I have a dataset saved in a permanent library, I want to use proc export to export dataset into an excel file. That excel file is a template so there are some title/date at the very top of the sheet. I want to export my dataset starting row 7 for example. My code looks like:

PROC EXPORT DATA = perm.dataset

dbms=xlsx

outfile= "&libnm./&file." label REPLACE;

sheet="data";

RUN;

Is there any options in proc export to allow me to do that?

PS: This is an auto-process without any manual work.

Thanks a lot.

Re: proc export a dataset into certain cells of excel file

Reeza — Tue, 13 Aug 2019 15:32:02 GMT

Try the RANGE option, but you need to include the Sheet name in the RANGE. If you don't know the end of the range just put a 0 to function as a wildcard.

range="Sheet1$A1:0"

I don't know if that's the exact format the range should be in, but hopefully that gives you the idea of what you need to do.

There's also a user custom written macro here that offers more control.
https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Additional-capabilities-when-exporting-data-to-Excel/m-p/442605?title=A_Poor/Rich_SAS_Users_Proc_Export