SAS: Export more dataset in one excel sheet with two columns space

[JohnMidd1111]

Hi guys,

I am in trouble with the following question: In general, I generate a lot of excel folder with SAS and I export each single dataset to another excel file, in order to have a lot of dataset with 2 columns that is the space between each dataset.

There exist a command in SAS that give to me a single excel file, considering a lot of dataset? For example, if I have 3 dataset in SAS, is possible to have the following output, with some command, as the picture below?

My desired output:

[ballardw]

First a minor editorial comment: Gaack!!!

It might make sense to have the datasets on separate sheets. The proposed layout, which shows no information about what any of the groups of columns would mean, such as the source data set what it should be used for is just asking for trouble. Almost worse is the possibility of repeated variable names. So what do you do when you get an Excel sheet that has 3 columns named "Name"? Do you know what sort of "name" any particular column represents? Remember you have not included any information that shows the source of the data.

Won't work with Proc Export in general.

Please describe exactly how you expect to use that exported data?

You could use a LIBNAME pointing to an Excel file and Copy data sets (with different names) to different sheets.

Or use ODS EXCEL to with Proc Print (or other output generating procedure). By default each table "printed" would go to a different sheet.

The code to accomplish such a thing is likely to be somewhat fragile, i.e. easy to break and not generate "desired" output.

[JohnMidd1111]

Hi, thanks for the answer. I need to have the following structure since I need to generate 10 dataset in 5 different pages, for some analysis that I need to do at work.
So for this reason, in general, I create each single dataset and with copy and paste I generate my result: 10 dataset (10 for each sheet), and after that I take each sheet for some analysis that are very very long.

So there exist in your opinion something useful to have an output similar to the picture?

[ballardw]

I am having a very hard time imagining any analysis that requires that layout.

Please describe the analysis.

It might be easier to do in SAS before any export.

[ChrisNZ]

One (messy) way if you really need to do this:

data PRINT;
  if _N_<=NOBS1 then set SASHELP.CLASS nobs=NOBS1;
  BL1=' '; BL2=' ';    label BL1='00'x BL2='00'x;
  if _N_<=NOBS2 then set SASHELP.AIR nobs=NOBS2;
  output;
  call missing (of _ALL_);
run;
                                   
ods excel file='%temp%\file.xls';
proc print noobs label; run;
ods _all_ close;