BookmarkSubscribeRSS Feed
Yennie
Calcite | Level 5
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
6 REPLIES 6
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User
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
Peter_C
Rhodochrosite | Level 12
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
Ksharp
Super User
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.
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User
Hi.Cynthia.
I learned that using DDE also can be without opening xls file.
SAS is a really amazing tool.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 826 views
  • 0 likes
  • 4 in conversation