BookmarkSubscribeRSS Feed
Gab12
Fluorite | Level 6
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
9 REPLIES 9
SASKiwi
PROC Star

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.  

Hao_Luo
SAS Employee

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#p...

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:

1.png

ballardw
Super User

@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#p...

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.

Gab12
Fluorite | Level 6

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.

ASimpleCaveman
Calcite | Level 5

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.

ballardw
Super User

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.

 

ASimpleCaveman
Calcite | Level 5
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??
SASKiwi
PROC Star

@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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3311 views
  • 5 likes
  • 6 in conversation