topic Re: ods tagsets.excelxp to a specific cell in an excel sheet in ODS and Base Reporting

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

AnetteD — Fri, 13 Nov 2015 10:34:31 GMT

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 ?

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

RW9 — Fri, 13 Nov 2015 11:02:32 GMT

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

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.

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

AnetteD — Fri, 13 Nov 2015 12:23:55 GMT

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

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

RW9 — Fri, 13 Nov 2015 12:48:35 GMT

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.