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 ?
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;
PG
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?
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.
PG
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;
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";
PG
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.
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.
@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!
PG
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.