outputting multiple data sets into excel workbook

Question

Another question. I have multiple data sets that generate ouput how can output these into one excel work sheet and apply my own formating. For example I have data set 1, data set 2, data set 3

each data set has two coloumns, for example
Col 1 Col 2
1    2 
3    4 
5    6

I want each data set to be in one worksheet and seperated by column , so in excel it should look like

Col 1 Col 2 Blank Col Col 1 Col 2 Blank Col

Somone told me I need to look at DDE for this is this true

Regards,

Answer 1

You can definitely do it using DDE. What DDE does it just simulates user's clicks at Excel's menus, buttons, cells etc. Here's an example how you can do that with macro loop for 3 datasets with names have1, have2 and have3. If you need more general solution (unknown number of datasets, with various number of variables, random datasets' names etc), the code should be updated, but its 'DDE-part' will be essentially pretty the same. One more assumption - your Excel workbook should be open during code execution. Though it can be also automated - Excel can be started and file can be open using DDE itself. You can find a very nice introduction into DDE here, where all these trick discussed in details.

data have1;
    input Col1 Col2;
datalines;
1 2
3 4
5 6
;
run;
data have2;
    input Col1 Col2;
datalines;
1 2
3 4
5 6
7 8
;
run;
data have3;
    input Col1 Col2;
datalines;
1 2
3 4
7 8
5 6
9 10
;
run;



%macro xlsout;

/*iterating through your datasets*/
%do i=1 %to 3;

    /*determine number of records in the current dataset*/
    proc sql noprint;
        select  count(*) into :noobs
        from have&i;
    quit;

    /*assign a range on the workbook spreadsheet matching to data in the current dataset*/
    filename range dde "excel|[myworkbook.xls]sas!r1c%eval((&i-1)*3+1):r%left(&noobs)c%eval((&i-1)*3+2)" notab;

    /*put data into selected range*/
    data _null_;
        set have&i;
        file range;
        put Col1 '09'x Col2;
    run;
%end;

%mend xlsout;
%xlsout

Answer 2

You cannot do exactly this with SAS (DDE is probably possible). I would suggest looking at SaviCells Pro.

http://www.sascommunity.org/wiki/SaviCells

http://www.savian.net/utilities.html

Answer 3

You could likely accomplish what you're asking through ODS TAGSETS.EXCELXP or the new ODS EXCEL (9.4 TS1M1). You would need to arrange the datasets ahead of time (ie, merge them together or transpose or whatnot to get one dataset with the right columns), however, or else use PROC REPORT or some other procedure to get them in the right format.