The SAS Output Delivery System and reporting techniques

ods tagsets.excelxp to a specific cell in an excel sheet

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

ods tagsets.excelxp to a specific cell in an excel sheet

Hi all

 

I would like to make several proc table and then have the two tables in the same excel sheet that is I want to control which cell in the same excel (tab) sheet I put the two tables in. I am using

ods tagsets.excelxp to output the tables to excel.

 

Does anybody know how to do that ?


Accepted Solutions
Solution
‎11-18-2015 06:56 AM
Super User
Super User
Posts: 7,942

Re: ods tagsets.excelxp to a specific cell in an excel sheet

Oh, then thats simply the options part, sorry, was thinking it was complicated:

ods tagsets.excelxp file="outputfile.xml" options=(sheet_interval=none);

proc tabulate data...;

run;

proc tabulate data...;

run;

ods tagsets.excelxp close;

 

You really don't want to use DDE, its pretty old and may/may not be supported, plus wouldn't have a lot of functionality anyways.

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: ods tagsets.excelxp to a specific cell in an excel sheet

It depends on which way round you want to do it.  If you feel more comforatble in SAS then create datset exactly as you want the output Excel file to look like, i.e. combine your current two tables into one, and have the data at the equivalent row you want.  Then report that.  I.e.

table1 

VAR1  VAR2

a         b

a         c

 

table2

VAR1  VAR2

f          g

f          h

 

Now I want to have the table 2 data at row 6 instead of row 4:

final_table

VAR1  VAR2

a         b

a         c

<missing>

<missing>

f          g

f          h

 

f will then start at row 6.  Alternatively you can proc report both datasets with an option of the ods tagsets.excelxp options=(sheet_interval=none);

 

Note however that control over placing is limited.

 

Another alternative, which gives you more control, is to output the data from SAS as CSV.  Now create an Excel template file which looks exactly as you want, and write a small VBA macro to read the CSV file and put the data where you want.

 

 

 

Occasional Contributor
Posts: 5

Re: ods tagsets.excelxp to a specific cell in an excel sheet

Thanks for quick reply. I dont think it solves my problem though as I have two different datasets that I am proc tabulating on. I then want the proc tabulate output table 1 and table 2 to go into the same (tab) excel sheet. See attached excel sheet for example

 

When I am googling on the net it looks like I have to use DDE, but I hope there is a simpler way

Solution
‎11-18-2015 06:56 AM
Super User
Super User
Posts: 7,942

Re: ods tagsets.excelxp to a specific cell in an excel sheet

Oh, then thats simply the options part, sorry, was thinking it was complicated:

ods tagsets.excelxp file="outputfile.xml" options=(sheet_interval=none);

proc tabulate data...;

run;

proc tabulate data...;

run;

ods tagsets.excelxp close;

 

You really don't want to use DDE, its pretty old and may/may not be supported, plus wouldn't have a lot of functionality anyways.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 716 views
  • 0 likes
  • 2 in conversation