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.
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.
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;
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.
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?
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.