Reporting Question

[1]

my output has 5 columns and 100 rows. I would like to output that into an excel file but what I want is to that columns A-E in Excel will contain the first 60 rows and the rest of the rows will be in columns G-K with the same heading as those in the first one...

[David_SAS]

The short answer is to use DATA step to get the output into the desired shape, then use ODS to render into EXCEL. I'm not a DATA step expert, so I'll wave my hands at the first step, which creates a data set called MERGE. The second step is simple:
[pre]
ods tagsets.excelxp file="merge.xls";
proc print data=merge; run;
ods tagsets.excelxp close;
[/pre]

-- David Kelley, SAS

[Cynthia_sas]

If you want column f to be blank, you'll have to create it in your data step. There may be more elegant ways of doing this, but here's one possibility.[pre]
data first60(rename=(region=areg product=bpro subsidiary=csub sales=dsal inventory=einv))
next40(rename=(region=greg product=hpro subsidiary=isub sales=jsal inventory=kinv));
set sashelp.shoes;
retain rownum 0 f ' ';
keep rownum region product subsidiary sales inventory f;
rownum + 1;
if _n_ le 60 then output first60;
if _n_ = 60 then rownum = 0;
if _n_ gt 60 and _n_ le 100 then output next40;
run;

data mergefile;
merge first60 next40; by rownum;
run;

options missing = ' ';
ods tagsets.excelxp file="mergefile.xls";
proc print data=mergefile(drop=rownum) noobs;
run;
ods tagsets.excelxp close;
options missing = .;
[/pre]
Since you said that you wanted the first 5 columns in A-E, then you have to drop rownum from the proc print step. Also, the variables have to be renamed in order for the final dataset to have the structure you want. And, keep in mind that the cells for the last 20 rows in columns G-K are technically "missing" so your numeric variables would normally show . for missing without the options statement.

I was thinking that there might be a custom table template solution/data step solution or an HTMLPANEL solution, but they don't just spring to mind.

Good luck with this!
cynthia