DATA Step, Macro, Functions and more

Creating a macro to print multiple tables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Creating a macro to print multiple tables

Dear Community. I have performed more than 30 regressions and all the results are outputted in the Work folder under different SAS datasets called Test1a, Test1b, ... Test 10a,Test10b, Test 10c.

Currently I am using a very inefficient code to print all the results and I am repeating the proc print every time. 

 

ods html file='Table1.html';
title "Table 1";

proc print data=test1a noobs;
title "Test1";
format coeff 8.4 t_stat 8.2; run;

proc print data=test1b noobs;
title "Test1b";
format coeff 8.4 t_stat 8.2; run;

proc print data=test1c noobs;
title "Test1c";
	format coeff 8.4 t_stat 8.2; run;

***********and so on;*
ods html close;

1. What is the best way to create a macro that will print all the Tables containing the name "Test". First I would like the output to be in the html format.

2. What is the best way to output all these regression results into a single Excel worksheet. What proc should I use? If Test1a, Test 1b, Test1c are similar is there a way to put them next to each other in the excel worksheet. Similarly Test2a, Test2b, Test2c are similar as well and it will be nice to have them together. At the end I need all the 30 regression tests in a Single worksheet. Let me know if there is an elegant SAS way to do this.

I appreciate your help.

 

 


Accepted Solutions
Solution
‎03-29-2018 03:35 AM
Super User
Posts: 10,280

Re: Creating a macro to print multiple tables

[ Edited ]
Posted in reply to Agent1592

First, wrap a macro definition around your proc print:

%macro print_result(tablename);
proc print data=&tablename noobs;
title "&tablename";
format coeff 8.4 t_stat 8.2;
run;
%mend;

Next, collect all tables that fit your pattern:

proc sql;
create table mytables as
select memname
from dictionary.tables
where libname = 'WORK' and memname contains 'TEST';
quit;

Finally, use that dataset to call the macro repeatedly:

data _null_;
set mytables;
call execute('%print_result(' !! trim(tablename) !! ');');
run;

Note that you can omit the macro definition and write the complete proc print into the call execute.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 580

Re: Creating a macro to print multiple tables

Posted in reply to Agent1592

Using a macro won't make the code more efficient, only the amount of code to be maintained will be reduced, but that code will be more difficult to understand than 30 proc prints. I am not an expert in creating excel-files, but you will, most likely, need a more code to put some tables next to each other.

 

Here is one approach to get the html-output. The macro contains proc print, only. The data-step calls the macro for each dataset found in work and named TABLE* - in sashelp.vtable all names are in upcase.

 

%macro print_table(Dataset=);
   title "%sysfunc(propcase(&Dataset.))";
   proc print data=&Dataset. noobs;
      format coeff 8.4 t_stat 8.2; 
   run;
   title;
%mend;


ods html file="table.html";

data _null_;
   set sashelp.vtable(where=(LibName = 'WORK' and MemName like 'TABLE%'));
   
   call execute(cats('%print_Table(Dataset=', MemName, ')'));
run;

ods html close;
Solution
‎03-29-2018 03:35 AM
Super User
Posts: 10,280

Re: Creating a macro to print multiple tables

[ Edited ]
Posted in reply to Agent1592

First, wrap a macro definition around your proc print:

%macro print_result(tablename);
proc print data=&tablename noobs;
title "&tablename";
format coeff 8.4 t_stat 8.2;
run;
%mend;

Next, collect all tables that fit your pattern:

proc sql;
create table mytables as
select memname
from dictionary.tables
where libname = 'WORK' and memname contains 'TEST';
quit;

Finally, use that dataset to call the macro repeatedly:

data _null_;
set mytables;
call execute('%print_result(' !! trim(tablename) !! ');');
run;

Note that you can omit the macro definition and write the complete proc print into the call execute.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 76

Re: Creating a macro to print multiple tables

Posted in reply to KurtBremser

Thank you sir. 

I am currently using this code to export into excel:

 

ods MSOffice2K body='Table1.xls';
data _null_;
set mytables;
call execute('%print_result(' !! trim(memname) !! ');');
run;
ods MSOffice2K close;

Is this the correct code? It is exporting but the tables are below one another. Is there a way to put them next to each other horizontally?

Super User
Posts: 10,280

Re: Creating a macro to print multiple tables

Posted in reply to Agent1592

@Agent1592 wrote:

Thank you sir. 

I am currently using this code to export into excel:

 

ods MSOffice2K body='Table1.xls';
data _null_;
set mytables;
call execute('%print_result(' !! trim(memname) !! ');');
run;
ods MSOffice2K close;

Is this the correct code? It is exporting but the tables are below one another. Is there a way to put them next to each other horizontally?


I'd put that up in a new question in the ODS community. The only thing that comes to my mind is creating a HTML table structure where each proc print result sits in its own cell in the master table.

 

See this simple example:

filename out '$HOME/sascommunity/side_by_side.html';

ods html body=out  (no_bottom_matter);
ods html close;

filename out '$HOME/sascommunity/side_by_side.html' mod;

data _null_;
file out mod;
put '<table>';
put '<tr>';
put '<td>';
run;

ods html file=out (no_top_matter no_bottom_matter);

proc print data=sashelp.class;run;

ods html close;

data _null_;
file out;
put '</td><td>';
run;

ods html file=out (no_top_matter no_bottom_matter);

proc print data=sashelp.class;run;

ods html close;

data _null_;
file out;
put '</td>';
put '</tr>';
put '</table>';
run;

ods html file=out (no_top_matter);
ods html close;

Spreadsheet programs can open such a HTML file. Tested with Excel 2013 and LibreOffice.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 165 views
  • 2 likes
  • 3 in conversation