topic Re: Assigning data to designated cells in Excel Spreadsheet in SAS Procedures

Assigning data to designated cells in Excel Spreadsheet

Yennie — Mon, 26 Jul 2010 02:22:56 GMT

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 A2:O90 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

Re: Assigning data to designated cells in Excel Spreadsheet

Cynthia_sas — Mon, 26 Jul 2010 05:19:42 GMT

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

Re: Assigning data to designated cells in Excel Spreadsheet

Ksharp — Tue, 27 Jul 2010 08:51:44 GMT

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

Re: Assigning data to designated cells in Excel Spreadsheet

Peter_C — Tue, 27 Jul 2010 13:04:13 GMT

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

Re: Assigning data to designated cells in Excel Spreadsheet

Ksharp — Wed, 28 Jul 2010 09:15:01 GMT

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.

Re: Assigning data to designated cells in Excel Spreadsheet

Cynthia_sas — Wed, 28 Jul 2010 14:55:19 GMT

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

Re: Assigning data to designated cells in Excel Spreadsheet

Ksharp — Wed, 28 Jul 2010 15:31:45 GMT

Hi.Cynthia.
I learned that using DDE also can be without opening xls file.
SAS is a really amazing tool.