topic Re: Exporting a table in a particular range in SAS Programming

Exporting a table in a particular range

Gab12 — Tue, 14 Mar 2023 18:57:24 GMT

Hello,

I would like to export 1 table In particular range of a sheet that already contains data. I have indicated in the code to put the export from cell B6.

proc export data=data
outfile="/Y/test/invoice" /* name and location of Excel file */
dbms=xlsx /* Excel file format */
replace;
range='B6:F6'; /* replace the file if it already exists */
sheet="base"; /* name of the sheet in Excel */
*putnames=no;/* start cell in Excel */
/* start from the second line without the headers */
run;

But with this code, it does export the table to the right cells but it overwrites what was already there.
I don't see what option to add to tell it to keep the data that is already in the file.

Thanks in advance for any help

Re: Exporting a table in a particular range

SASKiwi — Wed, 15 Mar 2023 03:24:39 GMT

By specifying a cell range you are telling SAS to overwrite any existing data in that range with data from your SAS dataset.

Try the XLSX LIBNAME engine and the PROC DATASETS APPEND statement to get closer to what you want.

Re: Exporting a table in a particular range

Hao_Luo — Wed, 15 Mar 2023 03:51:05 GMT

The range= option may not avaliable when exporting Microsoft Excel files using the XLS and XLSX Identifiers: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0msy4hy1so0ren1acm90iijxn8j.htm#p09degjribu2smn1eaqt5eq9on58

I recommend you the DDE method, it may be old, but very useful:

proc export data=sashelp.class outfile='C:\Profiles\test.xlsx' dbms=xlsx replace;
  newfile=yes;
  sheet='Sheet1';
run;

options noxwait noxsync;
x '"C:\Profiles\test.xlsx"';
%let rc=%sysfunc(sleep(5));      *Wait for opening Excel;

filename xlsx dde "excel|Sheet1!r23c2:r100c6";

data _null_;
  file xlsx;
  set sashelp.class;
  put name sex age height weight;
run;

The result:

Re: Exporting a table in a particular range

ballardw — Wed, 15 Mar 2023 05:35:40 GMT

@Hao_Luo wrote:

The range= option may not avaliable when exporting Microsoft Excel files using the XLS and XLSX Identifiers: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0msy4hy1so0ren1acm90iijxn8j.htm#p09degjribu2smn1eaqt5eq9on58

I recommend you the DDE method, it may be old, but very useful:

However the communications channels that DDE uses may be preempted by other programs.

I had one process that used DDE, been a few years, but when our IT department added Cisco Jabber to our suite of standard applications the process no longer worked while Jabber was active. In this case I was able to KILL the Jabber process using Windows Task Manager, closing the application window was not sufficient, and it would run. But determining which programs may be interfering could be a nightmare.

Re: Exporting a table in a particular range

Gab12 — Wed, 15 Mar 2023 13:52:42 GMT

Hello everyone
Thank you for your help. I will try to understand what you have suggested.
I don't have much experience with SAS, so I don't understand everything I see here.

Re: Exporting a table in a particular range

ASimpleCaveman — Thu, 04 Jan 2024 16:12:19 GMT

I am being told by sas that proc export does not allow

writing to a range yet you say you are doing it. I have

included your post to them for resolution. Any citations of

people writing to excel ranges would be appreciated.

Re: Exporting a table in a particular range

ballardw — Thu, 04 Jan 2024 17:49:49 GMT

One suspects that you asked a different question than just "export to range", such as the original poster of this thread that was OVERWRITING values already in the spreadsheet and not the desired behavior.

For example the online help for SAS 9.4.4 includes this portion:

A Range of Data in an Excel Worksheet

The SAS C Files Server that works with SAS/ACCESS treats an Excel workbook as a database, and a range (subset of cells in a worksheet) as a table. A range name must be defined in the Excel file before SAS can use it. A worksheet is treated as a special range. A worksheet name appended with a $ character is treated as a range.

Also there is a big difference between XLS, XLSX files and which versions of the Office suite are installed (or not) and what is possible with the Office files.

Re: Exporting a table in a particular range

SASKiwi — Thu, 04 Jan 2024 19:05:56 GMT

@ASimpleCaveman - The RANGE option is supported for both the EXCEL and EXCELCS DBMS options of PROC EXPORT but not for XLS and XLSX DBMSs. If your SAS servers run on Unix then the EXCEL option wont be available, but EXCELCS is if you have the SAS PC Files Server installed.

Re: Exporting a table in a particular range

Ksharp — Fri, 05 Jan 2024 05:50:55 GMT

You could try "START_AT=" option of ODS EXCEL.
https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Re: Exporting a table in a particular range

ASimpleCaveman — Fri, 05 Jan 2024 19:06:47 GMT

re Exporting a table in a particular range

|
|
|
| | |

|

|
|
| |
Exporting a table in a particular range

Hello, I would like to export 1 table In particular range of a sheet that already contains data. I have indicat...
|

|

|

he is using xlsx the same as i am so that is not an issue.
are yo saying that if i set the target xlsx up so that it has data inthe targeted cells and it has a named range it will work??