BookmarkSubscribeRSS Feed
BCNAV
Quartz | Level 8

I need to be able to place small datasets into the SAME excel spreadsheet at specific locations within that sheet. I have tried:

 

ods listing close;
ods html close;
goptions device=actximg;
%let dat = %sysfunc(today(),yymmdd6.);

ods excel file="h:\temp.xlsx";
ods excel options(embedded_titles="no" start_at="5,5" sheet_name="Sheet1" sheet_interval="none");
 
proc print data=EGTASK.NEW_PAIRS_FINAL;
title "Expense Report Year 2016";
run;

ods excel options (embedded_titles="no" start_at="50,30" sheet_name="Sheet1");
proc print data=EGTASK.NEW_PAIRS_FINAL;
title "Expense Report Year 2016";
run;
 
ods excel close;

It will place the first table in 5,5 as asked, but it will place the second table right under it (one line separating them) instead of 50,30. We need to place these at specific cells on the same sheet. Can this be done? Not sure what to do in this case.

 

Cheers!

5 REPLIES 5
Reeza
Super User

You can if your tables go down (linear) but if you want side by side tables that won't fly with ODS EXCEL. 

 

art297
Opal | Level 21

If you're using a Microsoft Window's system you can do it with the macro you can download from: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Art, CEO, AnalystFinder.com

 

 

 

BCNAV
Quartz | Level 8
Can this be done with another method? DDE?

Also, with respect to my example above, 50,30 won't work but 5,30 would? Thx
Reeza
Super User

@BCNAV wrote:
Can this be done with another method? DDE?



Yes, but DDE is a pain. The documentation for DDE has an example on how to write data out and someone else just had a DDE question and their code is mostly correct 🙂

 

@art297 macro is another really good option as well. 

 

For custom reports like that, I usually do a mix. 

I create an Excel template with the data in the linear form and then link the table data over to the displayed view, so how I export the data isn't really relevant. This works with reports that have consistent structure and you have a good idea of the number of rows/tables/graphs that are needed ahead of time. 

 

 

 

 

anjicam
Calcite | Level 5
Hi @BCNAV,
I am facing the same problem that you posted here.
I am really searching answer for this Question so long ago. Could you please answer me if you done that.

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!

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.

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
  • 5 replies
  • 7774 views
  • 0 likes
  • 4 in conversation