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

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

JohnMidd1111 — Thu, 24 Jun 2021 07:21:18 GMT

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:

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

ballardw — Thu, 24 Jun 2021 09:56:56 GMT

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.

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

JohnMidd1111 — Thu, 24 Jun 2021 10:47:16 GMT

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?

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

ballardw — Thu, 24 Jun 2021 14:45:24 GMT

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.

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

ChrisNZ — Fri, 25 Jun 2021 05:39:28 GMT

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;