Help using Base SAS procedures

Assigning data to designated cells in Excel Spreadsheet

Reply
Frequent Contributor
Posts: 78

Assigning data to designated cells in Excel Spreadsheet

Hi All,

I am not sure if SAS can do this. I would like to export data into an excel spreadsheet (I know this bit can be easily done) however, the hard bit is that I am hoping to assign the data to designated cells. say for example dataset A goes to Cell A2Smiley Surprised90 and dataset B does to B10:C30.

Hoping someone can help me out on this one!

Cheers!

Message was edited by: Yennie Message was edited by: Yennie
SAS Super FREQ
Posts: 8,864

Re: Assigning data to designated cells in Excel Spreadsheet

Hi:
You can't write to explicit cell locations using the Output Delivery System methods. ODS wants to recreate the entire spreadsheet. However, you can write to named ranges in a sheet using the LIBNAME Excel engine and maybe with PROC IMPORT.

The other alternative is to investigate the use of DDE or ODBC or OLE-DB.

cynthia
Super User
Posts: 10,023

Re: Assigning data to designated cells in Excel Spreadsheet

Hi.Just as Cynthia@SAS said ,you can do it by DDE.
But firstly you should create a xls file(e.g Op.xls),and before run the code below,you should open Op.xls;


[pre]
filename area1 DDE 'excel|c:\[Op.xls]Sheet1!r2c2:r50c20';
filename area2 DDE 'excel|c:\[Op.xls]Sheet1!r2c30:r50c40';

data _null_;
set sashelp.class;
file area1;
put name sex height weight;
file area2;
put name sex height weight;
run;
[/pre]



Ksharp
Valued Guide
Posts: 2,177

Re: Assigning data to designated cells in Excel Spreadsheet

one interesting thing we used to do with DDE, took advantage of the FILEVAR= option on the FILE statement.
The value of the FILEVAR was re-evaluated each time the code executed the FILE statement. That allowed the data step to write to any number of ranges with just the one file statement.
There are other and stronger reasons for avoiding DDE.

peterC
Super User
Posts: 10,023

Re: Assigning data to designated cells in Excel Spreadsheet

Hi.Peter.
I have no clue about this ,Sorry. I just saw something writed by Cynthia and write these codes. You should talk with some technical guys of SAS.They can give some detailed reason. It is also not convenient to open xls file firstly with DDE.
SAS Super FREQ
Posts: 8,864

Re: Assigning data to designated cells in Excel Spreadsheet

Hi:
There are many papers on DDE written by Koen Vyverman and they are very, very useful, if you are going to use DDE.

And if you Google for papers about SAS and DDE, in addition to the Vyverman papers, you will find this paper (by Cohen and Shields):
http://www.nesug.org/proceedings/nesug04/hw/hw01.pdf

In which they explain 2 ways to open Excel from within a SAS program.

cynthia
Super User
Posts: 10,023

Re: Assigning data to designated cells in Excel Spreadsheet

Posted in reply to Cynthia_sas
Hi.Cynthia.
I learned that using DDE also can be without opening xls file.
SAS is a really amazing tool.
Ask a Question
Discussion stats
  • 6 replies
  • 176 views
  • 0 likes
  • 4 in conversation