topic Re: proc export to specific unnamed range in xlsx file in SAS Programming

proc export to specific unnamed range in xlsx file

vassYesMan — Tue, 25 May 2021 16:08:51 GMT

I am using SAS version 9.4 and am trying to export some data to a specific unnamed range in an xlsx file. The data I have is stored in the UNIX environment and I want to export it to a windows xlsx file. I have checked the proc sentit and observed the necessary license ("SAS/ACCESS Interface to PC Files").

I looked into ods excel however the issue with this it cannot be used to change an existing file. Also, I believe this still does not meet my requirement to export data to a windows destination as it interacts with UNIX destinations.

I have since adopted a proc export and have managed to export to a sheet, but in the default position (A1). My code can be seen below (ignoring the XXXXs which are just illustrative for completion):

%Macro ExportToLAN(Dset,LAN_Name,sheet,range);

	proc export dbms=excelcs
		data=&dset.
		outfile="&LAN_Name."
		replace;
		sheet=&sheet.;
		range=&range.;
		server='XXXXXX';
		port=XXXX;
		SERVERUSER="%sysfunc(strip(&LAN_username.))";
		SERVERPASS="%sysfunc(strip(&LAN_password.))";
	run;

%Mend;

Running this macro results in a warning:

WARNING: This RANGE statement is not supported and is ignored in Export Procedure.

Does anyone have any idea on how to resolve this or better have any alternative solutions to exporting data to specific unnamed ranges?

Re: proc export to specific unnamed range in xlsx file

CarmineVerrell — Wed, 26 May 2021 13:24:45 GMT

Here is one example of how to use SAS Access to output to excel. In document there is also discussion , using DDE but please ignore this portion as this would not work on a server environment.

thanks

024-31: De-Mystifying the SAS® LIBNAME Engine in Microsoft Excel: A Practical Guide

Re: proc export to specific unnamed range in xlsx file

Tom — Wed, 26 May 2021 16:40:14 GMT

What I have seen suggested for this is to NOT write directly to a range in the target file.

Instead write a whole SHEET in the target file and use Excel features to show the value from the sheet t in the range where you want it.

Re: proc export to specific unnamed range in xlsx file

vassYesMan — Tue, 01 Jun 2021 08:14:10 GMT

I've had a read through this an do not think it is what I am looking for. The paper references named ranges.

The following may be what I am looking for but if I have multiple tables that I want in the same sheet, each step would involve overwriting existing data.

"To write data beginning elsewhere than default cell A1, first specify an empty named range in Excel. When writing to
that range SAS will begin in the top-left cell of the range and add columns or rows as necessary."

Also, this seems complicated - does it require creating several empty rows and columns in order to get your data where you want it?

Re: proc export to specific unnamed range in xlsx file

vassYesMan — Tue, 01 Jun 2021 08:19:16 GMT

Can you expand on this a little? With regards to excel features are you talking about setting an "=A1" where A1 contains some data I want? Or are you talking about a more sophisticated functionality I am missing.

I am exporting several small tables and they all appear on a single sheet. Currently, they are already referenced in the way above in order to format them ("=A1"). However, currently the only way I can include new tables is by copy and pasting them into the location required. Of course I am trying to get around this, but by minimising disruption to my existing spreadsheet design (which may not be optimal, however this is a separate issue).

Re: proc export to specific unnamed range in xlsx file

Bravez — Wed, 20 Apr 2022 20:00:45 GMT

Probably you can take a look at this new solution, have similar effects like DDE (i.e. inject values to any cell of choice in an Excel/ Word/ PowerPoint template), which works well for cloud/citrix server environment. One-Click Report Automation - An automated and user friendly workflow for efficient, flexible, and extendable analysis and reporting (sas.com)