topic export multiple Tables in 1 sheet in SAS Programming

export multiple Tables in 1 sheet

Konkordanz — Fri, 23 Jul 2021 07:54:29 GMT

Hi,

I coudnt fine a solution on the Internet...so:

I have a huge dataset with regional data.I created 89 summarized values (Col1) with proc summary, transpose and so on. The result is table 1 (Region Europe) with 89 lines and 4 columns.

The aim: Create 20 further tables of different regions of europe with exactly the same procedure (with the same groupes of column 1 [3122B, 4231A, ...] and the same table header [A, B02, C23, Dd]). All these tables shall be exported in 1 excel sheet; table 2 below table 1, table 3 below table 2 and so on. After the end of table 1 there should be 2 empty lines, 1 title, 1 emtpy line and then the next table follows (first the table-header and then the new values). Something like this:

Titel 1 (Europe)

	A	B02	C23	Dd
3122B	…	…	…	…
4231A	…	…	…	…
4321X	…	…	…	…
5652Z	…	…	…	…
n	…	…	…	…


Title 2 (West_EU)

	A	B02	C23	Dd
3122B	…	…	…	…
4231A	…	…	…	…
4321X	…	…	…	…
5652Z	…	…	…	…
n	…	…	…	…

Title 3 (germany)

	A	B02	C23	Dd
3122B	…	…	…	…
4231A	…	…	…	…
4321X	…	…	…	…
5652Z	…	…	…	…
n	…	…	…	…

How can I solve this problem? Is it maybe possible to use the same procedure/code of table 1 via a Loop and different filters for the regions? Like:

Loop1: Procedere without Filters (=Europe) for the first table 1

Loop2: Procedere with Filter for Western Europe for the table 2

Loop 3: Procedere with Filter for Germany for the table 3

Loop 4: [...]

And afterwards I use an export-command to put all these tables in 1 excel-sheet? Do you have an idea how to do that?

Thank you for hinks!

🙂

Re: export multiple Tables in 1 sheet

andreas_lds — Fri, 23 Jul 2021 08:43:09 GMT

The export could be done with one proc print, if the data is one dataset:

proc sort data=sashelp.cars out=work.cars;
   by Origin;
run;

ods excel file="&Benutzer\temp\multitbable.xlsx" options(sheet_interval='none');

proc print data=work.cars;
   by Origin;
   
   var Model Type Horsepower;
run;

ods excel close;

I have no idea on how to create the data in your scenario, your description is to vague. You will have to post data in usable form and the code you already have to suggest something useful.

Re: export multiple Tables in 1 sheet

Reeza — Fri, 23 Jul 2021 14:40:15 GMT

I suspect you'll need macros then to automate this. Sometimes you can use BY group processing instead, which is often a good method as well. Pick which approach you'd like to learn and go down that road.

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@Konkordanz wrote:

Hi,

I coudnt fine a solution on the Internet...so:

I have a huge dataset with regional data.I created 89 summarized values (Col1) with proc summary, transpose and so on. The result is table 1 (Region Europe) with 89 lines and 4 columns.

The aim: Create 20 further tables of different regions of europe with exactly the same procedure (with the same groupes of column 1 [3122B, 4231A, ...] and the same table header [A, B02, C23, Dd]). All these tables shall be exported in 1 excel sheet; table 2 below table 1, table 3 below table 2 and so on. After the end of table 1 there should be 2 empty lines, 1 title, 1 emtpy line and then the next table follows (first the table-header and then the new values). Something like this:

Titel 1 (Europe)

A B02 C23 Dd

3122B … … … …

4231A … … … …

4321X … … … …

5652Z … … … …

n … … … …

Title 2 (West_EU)

A B02 C23 Dd

3122B … … … …

4231A … … … …

4321X … … … …

5652Z … … … …

n … … … …

Title 3 (germany)

A B02 C23 Dd

3122B … … … …

4231A … … … …

4321X … … … …

5652Z … … … …

n … … … …

How can I solve this problem? Is it maybe possible to use the same procedure/code of table 1 via a Loop and different filters for the regions? Like:

Loop1: Procedere without Filters (=Europe) for the first table 1

Loop2: Procedere with Filter for Western Europe for the table 2

Loop 3: Procedere with Filter for Germany for the table 3

Loop 4: [...]

And afterwards I use an export-command to put all these tables in 1 excel-sheet? Do you have an idea how to do that?

Thank you for hinks!

🙂