BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi listers,

I am having some difficulties to reformat the GLM LSMeans difference
matrix with the TEMPLATE PROC.

Below is a small portion of code part of a big reporting program.

ODS select Diff ;
proc GLM data = TEMPSAS (keep=V1 V2 RESP);
class V1 V2;
model RESP = V1*V2 / NOUNI;
lsmeans V1*V2 / TDIFF ;
run;



Variable V1 has 3 values :
FR
IT
JA

And V2 :
Undergrad (U)
Graduate (G)

I want outputted from ODS :

- the LSmeans difference matrix testing for the hypotheses H0 : LSmean(i)=
LSMean(j) for all i,j.


which is outputted as :
- row : 1 2 3 4 5 6 (sequential numbering of the combinations)
- column : P1 P2 P3 P4 P5 P6 ("P" prefix + sequential numbering of the
combinations)
- table header : Least Squares Means for Effect V1*V2
t for H0: LSMean(i)=LSMean(j) / Pr > |t|
Dependent Variable: RESP

I have several questions :

1. How can I modify the table header dynamically (actually RESP isn't
the only var to be tested, this is all part of a macro) so that it
would print only the variable name "RESP".

2. How can I modify the col. headers so that they fit the label combinations, ie
FR-U FR-G IT-U IT-G JA-U JA-G instead of
P1 P2 P3 P4 P5 P6

3. same thing for the row headers as it is a symmetric matrix.

I have been searching in all the templates for this procedure without
any success by now.

Thanks in advance for your help,

Erwan
5 REPLIES 5
Kevin_SAS
SAS Employee
Could you supply us with a small working data set for this example?
Cynthia_sas
SAS Super FREQ
Hi:
If you put an ODS TRACE ON; at the top of your code, you should see this in the SAS log:
[pre]
Output Added:
-------------
Name: Diff
Label: Difference Matrix
Template: stat.GLM.PDiff
Path: GLM.LSMEANS.v1_v2.resp.Diff
Label Path: 'The GLM Procedure'.'Least Squares Means'.'v1*v2'.'resp'.'Difference Matrix'
-------------

[/pre]

The template that you would look to change would be stat.GLM.PDiff in the Table template folder for stat.GLM -- this folder
is located in SASHELP.TMPLMST.

To see the source statements for this template, you can submit:
[pre]
proc template;
source stat.GLM.PDiff / store=sashelp.tmplmst;
run;

[/pre]

What this shows you is that your PDiff template has a parent template of stat.GLM.StackedMatrix
(see parent= statement):
[pre]
27 proc template;
28 source stat.GLM.PDiff / store=sashelp.tmplmst;
NOTE: Template source is from SASHELP.TMPLMST (read-only file). The source statements will be generated so that running them will write to the current ODS
template path.
NOTE: Path 'Stat.GLM.PDiff' is in: SASHELP.TMPLMST.
define table Stat.GLM.PDiff;
notes "PDiff matrix of Least Squares Means";
parent = Stat.GLM.StackedMatrix;
double_space = OFF;
end;
29 run;

[/pre]

By the time you get done tracing inheritance, you will find that the ultimate ancestor for the PDiff template is the Common.StackedMatrix
table template. You can follow this inheritance path yourself by submitting:
[pre]
proc template;
source stat.GLM.PDiff / store=sashelp.tmplmst;
source stat.GLM.StackedMatrix / store=sashelp.tmplmst;
source Common.StackedMatrix / store=sashelp.tmplmst;
run;

[/pre]

Now, you can either start making changes to stat.GLM.PDiff or you can make changes to Common.StackedMatrix. At any rate, your beginning point
is here. To make changes to the table template, you would change the DEFINE block for your columns of interest. Table templates have 2 ways to
change values dynamically (such as in a header or label) -- there are some keywords you can use (such as _LABEL_ -- but those are not
relevant in your case, because you are dealing with calculated items and not variables that you name in a LABEL statement.
The other method is through the use of either the DYNAMIC statement or the MVAR statement to pass dynamic information to the table template.

Some examples of this can be found in the documentation for ODS Table templates. Be careful to look for TABLE template documentation
and not STYLE template documentation.

cynthia
Cynthia_sas
SAS Super FREQ
Hi:
After some consultation with the ODS developer for Table templates, we came up with this solution. It still requires some hard-coding of what you want, primarily in a PROC FORMAT. The program uses a custom TABLE template with an output dataset from PROC GLM. I used SASHELP.PRDSALE as my test dataset because it has some variables with the correct number of levels as your example.

Here's the program -- with comments. There's a LOT here and you'll have to read the doc on TABLE templates to see how I'm using stacking in my table template to replicate what happens normally inside PROC GLM. We tried, at first to send dynamic column labels to PROC GLM, but it wasn't cooperating. So this is still a TABLE template solution, you just have to create an output dataset from PROC GLM and then invoke the new table template, MYMATRIX, using DATA _NULL_.

cynthia

[pre]
** make some data;
** country has 3 values and prodtype has 2 values;
data tempsas;
set sashelp.prdsale;
v1 = country;
v2 = prodtype;
resp = predict;
run;

** create an output dataset from proc GLM;
ODS select Diff ;
ods output diff=work.pdiff;
proc GLM data = TEMPSAS (keep=V1 V2 RESP);
class V1 V2;
model RESP = V1*V2 / NOUNI;
lsmeans V1*V2 / TDIFF ;
run;
quit;

** make a format based on what you want the column headers ;
** and row formats to be;
** the column headers are formatted with $mh.;
** and the rows are formatted with $rn.;
proc format;
value $mh
'_1' = 'FR-U'
'_2' = 'FR-G'
'_3' = 'IT-U'
'_4' = 'IT-G'
'_5' = 'JA-U'
'_6' = 'JA-G';
value $rn
' 1' = 'FR-U'
' 2' = 'FR-G'
' 3' = 'IT-U'
' 4' = 'IT-G'
' 5' = 'JA-U'
' 6' = 'JA-G';
run;

** make the table template with the possibility;
** of 10 columns and 10 rows;
ods path work.tmp(update)
sashelp.tmplmst(read);

proc template;
define table mymatrix;
column rowname (_1 P1) (_2 P2) (_3 P3) (_4 P4)
(_5 P5) (_6 P6) (_7 P7) (_8 P8)
(_9 P9) (_10 P10);

define column rowname;
style = rowheader;
header = _label_;
end;
define column _1;
header = _label_;
just=r;
end;
define column _2;
header = _label_;
just=r;
end;
define column _3;
header = _label_;
just=r;
end;
define column _4;
header = _label_;
just=r;
end;
define column _5;
header = _label_;
just=r;
end;
define column _6;
header = _label_;
just=r;
end;
define column _7;
header = _label_;
just=r;
end;
define column _8;
header = _label_;
just=r;
end;
define column _9;
header = _label_;
just=r;
end;
define column _10;
header = _label_;
just=r;
end;
end;
run;

** initialize all macro variables to NULL;
** as prep for dynamically assigning the labels and column headers;
ods listing;
%let cl1 =;
%let cl2 =;
%let cl3 =;
%let cl4 =;
%let cl5 =;
%let cl6 =;
%let cl7 =;
%let cl8 =;
%let cl9 =;
%let cl10 =;

** find out all the variables that begin with '_' -- these were created;
** by PROC GLM;
** and build a string that can get passed to proc datasets as a ;
** series of numbered macro variables;
proc sql ;
select catt(name,'=','"',put(name,$mh.),'"') as chglabl
into :cl1-:cl10
from dictionary.columns
where libname = 'WORK' and
memname = 'PDIFF' and
name contains '_';
quit;

** Use the macro variables here;
** if any macro variables were not set above, then;
** you will basically issue an "empty" label statement:;
/* label ; */
** which would be ignored by proc datasets;
** but that is why macro variables have to be initialized;
** to NULL up above;
proc datasets library=work nolist;

modify pdiff;
format rowname $rn.;

label &cl1; label &cl2;
label &cl3; label &cl4;
label &cl5; label &cl6;
label &cl7; label &cl8;
label &cl9; label &cl10;
quit;

** double check with proc contents that the labels are correct;
proc contents data=work.pdiff;
run;

** use the table template;
ods html file='c:\temp\glm_data_null.html'
style=sasweb;

data _null_;
set work.pdiff;

file print
ods=(template='mymatrix');
put _ods_;
run;

ods _all_ close;

**reset listing and the ODS PATH;
ods listing;
ods path sasuser.templat(update)
sashelp.tmplmst(read);
[/pre]
deleted_user
Not applicable
Hi Cynthia,

Many thanks for your answer. I have used it and it worked BUT the problem is that my banner is dynamic.


Sometimes the banner has 5 items (FR-U FR-G IT-U IT-G JA-U) and other times 3 items (FR-U FR-G IT-G) depending ont he variables to be tabulated.


With the solution you provided and the hardcoding of some values, it obviously wasn't working because the banner items are written sequentially and do not reflect the "true" banner (when items are missing) etc

Below is part of the macros I am using so you can better understand the problem I am facing :

%macro tabsB2 (break1=,break2=,n=)/STORE ;
proc tabulate data=TEMPSAS (keep=&break1 &break2 &&AVAR&n.);
class &&ABREAK1&n. &&ABREAK2&n.;
var &&AVAR&n.;
table N*(&&AVAR&n.) mean*((&&AVAR&n.)*f=4.1), all='Total' &&ABREAK1&n.=&&ABREAK1&n. &&ABREAK2&n.=&&ABREAK2&n. &&ABREAK1&n.=&&ABREAK1&n.*&&ABREAK2&n.=&&ABREAK2&n. / misstext=" " ;
run;
%proba_list
%end;
%mend tabsB2;


where the ABREAK1, ABREAK2, AVAR are arrays of macro variables.
ABREAK1 : contains the countries (FR, GE, IT, SP, UK, US)
ABREAK2 : contains the student type (U, G, PG)

AVAR : contains the variables to be tabulated

Some variables only have values for some of the combinations ABREAK1*ABREAK2 and that is why there is a problem when hardcoding the combination of the breaks values that is used with the GLM proc.

the macro %proba_list is the part that you sent me :

%macro proba_list /STORE;
%cptlist(&&AVAR&n.)
%do i=1 %to &nbvar;
ods exclude all;
ods output diff=work.pdiff (keep= rowname P1 P2 P3 P4 P5 P6 P7 P8 P9);
TITLE %sysfunc(cat(Statistical significance for , %scan(&&AVAR&n,&i)));
proc GLM data = TEMPSAS (keep=&break1 &break2 %scan(&&AVAR&n.,&i));
class &&ABREAK1&n. &&ABREAK2&n.;
model %scan(&&avar&n.,&i) = &&ABREAK1&n.*&&ABREAK2&n. / NOUNI;
lsmeans &&ABREAK1&n.*&&ABREAK2&n. / TDIFF ;
run;

proc sql noprint;
select catt(name,'=','"',put(name,$mh.),'"') as chglabl
into :cl1-:cl10
from dictionary.columns
where libname = 'work' and
memname = 'PDIFF' and
name contains 'P';
quit;

proc datasets library=work nolist;
modify pdiff;
format rowname $rn.;
label &cl1; label &cl2;
label &cl3; label &cl4;
label &cl5; label &cl6;
label &cl7; label &cl8;
label &cl9; label &cl10;
quit;
proc contents data=pdiff;
run;
ods exclude none;
data _null_;
set pdiff;
file print
ods=(template='mymatrix');
put _ods_;
run;
%end;
%mend proba_list;

How can I make the banner dynamic?

Thanks in advance ffor your help

Erwan
deleted_user
Not applicable
Hi again,

I managed to fix the problem with the following macro :

%macro search_fmts (break1=,break2=,var=) /STORE;
proc sql noprint;
select distinct &break1,&break2
into : cnt1 - :cnt10, :sett1 - :sett10
from WORK.TEMPSAS
where &var ne . ;
quit;
%let B1= &cnt1 &sett1;
%let B2= &cnt2 &sett2;
%let B3= &cnt3 &sett3;
%let B4= &cnt4 &sett4;
%let B5= &cnt5 &sett5;
%let B6= &cnt6 &sett6;
%let B7= &cnt7 &sett7;
%let B8= &cnt8 &sett8;
%let B9= &cnt9 &sett9;
%let B10= &cnt10 &sett10;


proc format;
value $mh
'P1' = &B1
'P2' = &B2
'P3' = &B3
'P4' = &B4
'P5' = &B5
'P6' = &B6
'P7' = &B7
'P8' = &B8
'P9'= &B9
'P10'= &B10;

value $rn
' 1' = &B1
' 2' = &B2
' 3' = &B3
' 4' = &B4
' 5' = &B5
' 6' = &B6
' 7' = &B7
' 8' = &B8
' 9' = &B9
' 10' = &B10;
run;
%mend search_fmts;

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
  • 5 replies
  • 870 views
  • 0 likes
  • 3 in conversation