topic Re: How to combine similar output tabels in SAS output ? in Graphics Programming

How to combine similar output tabels in SAS output ?

stan — Sun, 24 Jun 2012 09:43:04 GMT

I want to compare types of different covariance structures in PROC MIXED. But I get a lot of similar tables in the output and their analysis is inconvenient. For example, Fit Statistics, Estimated R Matrix, Estimated R Correlation Matrix, and others. It would be nice to know how to combine them in a concise and readable manner. For instance, for the Fit Statistics tables:

FIT SATISTICS	UN	CS	AR(1)	...
-2 Res Log Likelihood	.	.	.	.
AIC (smaller is better)	.	.	.	.
AICC (smaller is better)	.	.	.	.
BIC (smaller is better)	.	.	.	.

How to do this ?

Re: How to combine similar output tabels in SAS output ?

PGStats — Sun, 24 Jun 2012 17:36:26 GMT

Hi, This is how I do this sort of thing (with example data from PROC MIXED doc) :

/* Example data */

data rc;
input Batch Month @@;
Monthc = Month;
do i = 1 to 6;
     input Y @@;
     output;
end;
datalines;
1   0 101.2 103.3 103.3 102.1 104.4 102.4
1   1   98.8 99.4 99.7 99.5    .     .
1   3   98.4 99.0 97.3 99.8    .     .
1   6 101.5 100.2 101.7 102.7    .     .
1   9   96.3 97.2 97.2 96.3    .     .
1 12   97.3 97.9 96.8 97.7 97.7 96.7
2   0 102.6 102.7 102.4 102.1 102.9 102.6
2   1   99.1 99.0 99.9 100.6    .     .
2   3 105.7 103.3 103.4 104.0    .     .
2   6 101.3 101.5 100.9 101.4    .     .
2   9   94.1 96.5 97.2 95.6     .     .
2 12   93.1 92.8 95.4 92.2   92.2 93.0
3   0 105.1 103.9 106.1 104.1 103.7 104.6
3   1 102.2 102.0 100.8 99.8    .     .
3   3 101.2 101.8 100.8 102.6    .     .
3   6 101.1 102.0 100.1 100.2    .     .
3   9 100.9 99.5 102.2 100.8    .     .
3 12   97.8 98.3 96.9 98.4 96.9 96.5
;

/* Create an empty table to gather stats */

proc sql;
create table fits (covStruct character(100), descr character(25), value numeric);

/* Define a macro to append stats */

%macro fits(fs=fs,mi=mi);
proc sql;
delete from fits where covStruct=(select value from &mi where descr="Covariance Structure");
insert into fits (covStruct, descr, value)
select &mi..value, &fs..descr, &fs..value
from &fs., &mi. where &mi..descr="Covariance Structure";
%mend fits;

/* Do an analysis, get stats in ODS tables */

proc mixed data=rc;
   class Batch;
   model Y = Month / s;
   random Int Month / type=un sub=Batch s;
   ods output FitStatistics=fs ModelInfo=mi;
run;

/* Call the macro */
%fits;

/* Another covariance structure */

proc mixed data=rc;
   class Batch;
   model Y = Month / s;
   random Int Month / type=cs sub=Batch s;
   ods output FitStatistics=fs ModelInfo=mi;
run;

/* Gather more stats */
%fits;

/* And so on ... */

/* Reshape the stats table */

proc sort data=fits; by descr covStruct; run;

proc transpose data=fits out=want(drop=_name_);
by descr;
id covStruct;
run;

Re: How to combine similar output tabels in SAS output ?

stan — Sat, 18 Aug 2012 20:59:12 GMT

Dear PG, thanks for your reply. I tried to implement the lines but failed...

Could you please provide a link for a general paper(s) on macros in SAS?

Re: How to combine similar output tabels in SAS output ?

PGStats — Sat, 18 Aug 2012 21:41:39 GMT

Please! Somebody help Stan. The little I know about SAS macros, I learned by reading the doc. I avoid using macros as much as possible.

Re: How to combine similar output tabels in SAS output ?

art297 — Sat, 18 Aug 2012 22:19:28 GMT

Stan,

Not sure if I can help, as PG knows stats a lot more than I do and I can only guess what he was recommending.

The code wouldn't run as both proc sql calls were missing quit; statements.

Try the following:

/* Example data */

data rc;

input Batch Month @@;

Monthc = Month;

do i = 1 to 6;

input Y @@;

output;

end;

datalines;

1 0 101.2 103.3 103.3 102.1 104.4 102.4

1 1 98.8 99.4 99.7 99.5 . .

1 3 98.4 99.0 97.3 99.8 . .

1 6 101.5 100.2 101.7 102.7 . .

1 9 96.3 97.2 97.2 96.3 . .

1 12 97.3 97.9 96.8 97.7 97.7 96.7

2 0 102.6 102.7 102.4 102.1 102.9 102.6

2 1 99.1 99.0 99.9 100.6 . .

2 3 105.7 103.3 103.4 104.0 . .

2 6 101.3 101.5 100.9 101.4 . .

2 9 94.1 96.5 97.2 95.6 . .

2 12 93.1 92.8 95.4 92.2 92.2 93.0

3 0 105.1 103.9 106.1 104.1 103.7 104.6

3 1 102.2 102.0 100.8 99.8 . .

3 3 101.2 101.8 100.8 102.6 . .

3 6 101.1 102.0 100.1 100.2 . .

3 9 100.9 99.5 102.2 100.8 . .

3 12 97.8 98.3 96.9 98.4 96.9 96.5

;

/* Create an empty table to gather stats */

proc sql;

create table fits (covStruct character(100), descr character(25), value numeric);

quit;

/* Define a macro to append stats */

%macro fits(fs=fs,mi=mi);

proc sql;

delete from fits where covStruct=(select value from &mi where descr="Covariance Structure");

insert into fits (covStruct, descr, value)

select &mi..value, &fs..descr, &fs..value

from &fs., &mi. where &mi..descr="Covariance Structure";

quit;

%mend fits;

/* Do an analysis, get stats in ODS tables */

proc mixed data=rc;

class Batch;

model Y = Month / s;

random Int Month / type=un sub=Batch s;

ods output FitStatistics=fs ModelInfo=mi;

run;

/* Call the macro */

%fits;

/* Another covariance structure */

proc mixed data=rc;

class Batch;

model Y = Month / s;

random Int Month / type=cs sub=Batch s;

ods output FitStatistics=fs ModelInfo=mi;

run;

/* Gather more stats */

%fits;

/* And so on ... */

/* Reshape the stats table */

proc sort data=fits; by descr covStruct; run;

proc transpose data=fits out=want(drop=_name_);

by descr;

id covStruct;

run;

Re: How to combine similar output tabels in SAS output ?

PGStats — Sun, 19 Aug 2012 02:18:26 GMT

Hi Art. why the double ampersands in

select &&mi..value, &&fs..descr, &&fs..value

and the single ampersands on the next line

from &fs., &mi. where &mi..descr="Covariance Structure";

Re: How to combine similar output tabels in SAS output ?

art297 — Sun, 19 Aug 2012 02:50:17 GMT

Early guessing on my part but one that doesn't affect anything. I've seen the quality of your code thus, when you said help was needed, I assumed something was wrong. Of course, I didn't stop to think that nothing might be wrong.

I first ran the initial datastep, the first proc sql, and then compiled the macro. When I saw that proc sql was still running at that point, I presumed that might be the locus of the non-existent problem. From there, I started changing a number of things until I finally got to the point of deciding that the code appeared to be running as intended.

Re: How to combine similar output tabels in SAS output ?

Patrick — Sun, 19 Aug 2012 03:09:46 GMT

What about using Proc Append for data collection?

/* Clean environment from leftovers of previous runs */

proc datasets lib=work nolist nowarn memtype=(view data);
delete fits Vfits ;
quit;

/* Define a macro to append stats */
%macro fits(fs=fs,mi=mi);
proc sql;
/* delete from fits where covStruct=(select value from &mi where descr="Covariance Structure");*/
    create view Vfits as
    select
        &mi..value as covStruct length=100 format=$100.
      , &fs..descr as descr length=25 format=$25.
      , &fs..value as value length=8 format=8.
    from &fs., &mi.(where=(descr="Covariance Structure"));
quit;

proc datasets lib=work nolist nowarn;

append base=fits data=Vfits;

run;

quit;

%mend fits;

I don't understand why you're using " delete from fits where covStruct=(select value from &mi where descr="Covariance Structure"); " and looking at the log there was nothing to delete using the sample data.

...but then I don't understand Proc Mixed - so may be this deletion would need to be added again to above macro.

Re: How to combine similar output tabels in SAS output ?

PGStats — Sun, 19 Aug 2012 12:27:06 GMT

@Patrick The delete step is because, typically one will explore different models interactively and accumulate stats along the way. So it is likely that you will try the same covariance structure more than once. The delete step keeps the latest version of your exploration. An alternate approach is to include a timestamp with the accumulated stats data.

@Art I should have been clearer in my call for help. I think what was needed by OP is a good reference to learn macro programming. I might benefit from it too!

Re: How to combine similar output tabels in SAS output ?

art297 — Sun, 19 Aug 2012 13:05:13 GMT

Then I will sit by the sidelines and wait for comments from those who have actually read the books .. which I haven't. That said, I will guess that Art Carpenter's book will be in the group ( http://www.sascommunity.org/wiki/Carpenter%E2%80%99s_Complete_Guide_to_the_SAS%C2%AE_Macro_Language,_Second_Edition_by_Art_Carpenter )

And, if one can find the time to read a LOT of historical SAS-L posts, just following the threads that Ian Whitlock has been involved in is an invaluable experience ( Search the SAS-L Newsgroup ) searching for IW1

Re: How to combine similar output tabels in SAS output ?

Cynthia_sas — Sun, 19 Aug 2012 15:22:52 GMT

Hi:

Before diving into Carpenter or Whitlock (both excellent on the topic of macro). I always suggest a paper that touches on almost all the macro facility can do without being overwhelming. The paper was written by one of our SAS instructors and is an excellent general overview (with very simple examples) that explains all the basics:

http://www2.sas.com/proceedings/sugi28/056-28.pdf

In addition to the other books/papers mentioned, I really like Michele Burlew's book "SAS Macro Programming Made Easy" (https://support.sas.com/pubscat/bookdetails.jsp?catid=1&pc=60560), too. It was the first book on SAS Macro programming that I read and between that and the Carpenter books and papers, I think you have perfect complements to the documentation on the SAS Macro Facility. But, if you don't want to buy books, there are a LOT of user group papers on the Macro Facility too.

SAS Macro Programming is often misunderstood. I think of it as nothing more than a big, invisible typist, whose only purpose in life is to generate code for SAS compilation and execution. The SAS Macro facility is a tireless typist, so I can have the macro facility generate a list of 1000 variables over and over again. Or, I can have it generate the same program over and over again and have it change 1 thing for each iteration of code generation (or multiple things). The SAS Macro facility is also sort of like an specialized stenographer -- who uses a special "shorthand" to transcribe or translate my macro language instructions into working (compilable, executable) SAS code. I know that there are a lot of "canned" macro programs out in the world to do specialized things and a lot of times, they are like a "black box" and you don't have to worry about what they do or how they work. Hopefully, those canned macro programs come well documented.

Sometimes, however, someone designs a macro program and releases it into the wild without fully explaining the data structure that they assume or the parameters that the macro expects. In that case, understanding how macro programs and macro variables work will make the difference between understanding how to fix what's wrong and getting back to work or being stranded (as my Dad used to say) up the creek without a paddle.

cynthia