BookmarkSubscribeRSS Feed
Konkordanz
Pyrite | Level 9

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)
    
     
 AB02C23Dd
3122B
4231A
4321X
5652Z
n
     
     
Title 2 (West_EU)
    
     
 AB02C23Dd
3122B
4231A
4321X
5652Z
n
     
Title 3 (germany)
    
     
 AB02C23Dd
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!

🙂

2 REPLIES 2
andreas_lds
Jade | Level 19

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.

Reeza
Super User

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!

🙂




Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1006 views
  • 0 likes
  • 3 in conversation