- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 e...
SAS Hackathon 2022 - RepTik Analytics Solution, www.reptik.swiss
Easy report generation cross-platform. Create, format, and modify your tables & reports with the ease of Office Software and fill in with data from any source automatically via RepTik PDR technology.