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!
🙂
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.
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-Ap...
@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!
🙂
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.