BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
andreas_lds
Jade | Level 19

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;
Kurt_Bremser
Super User

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.

Agent1592
Pyrite | Level 9

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?

Kurt_Bremser
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 2722 views
  • 2 likes
  • 3 in conversation