How can I Concatenate more than two columns with different variables?

[DJ20]

Hi everyone,

I have calculated each column of the ANOVA table for the factorial design I have been working at. Now I need to combine them together to have the full ANOVA table:

I would like to define ANOVA to equal the concatenation of the Degrees of Freedom, Sum of Squares, Mean Square, F-ratio, P-value with a column of (the main factors and interaction between factors given in character variables):

ANOVA = Factors || DF || SS || MS || Fratio || Pvalue ;
print ANOVA;

 However, because the factors are in character variable rather than numerical its given me the following error:

ERROR: All specified variables must be the same type.

So I changed it to the following. I was able to adjust the format of specific columns which is even better. However with the method below I cant label the graph as ANOVA !!.

print Factors || DF SS MS[format=10.03] Fratio[format=10.03] Pvalue[format=10.03];

Can anyone help?

THanks,

DJ

[Reeza]

If you can drop out of IML for that portion, it would probably be the easiest solution.

[IanWakeling]

Here are some suggestions of ways to work around this problem.   As it's only the 1st column that is character, then the simplest solution is just to use rownames -  so form a numeric matrix called ANOVA by concatenation and then do

print ANOVA [rowname=factors label='ANOVA table'];

Note that it is possible to use commas in a print statement to move to the next line so something like

print 'ANOVA table',fac DF [format=3.0] SS MS F p [format=pvalue6.4];

will give you something with a title above the table.  The pvalue SAS format can be useful here as it will print "<.0001" for very small values.

Alternatively you can investigate the Table data type in IML which allows for mixed columns.  Rick has  blogged about this here , but if your IML version is not up to date, you may not have this feature.

[DJ20]

I ended up doing the following:

COL = {"DF", "SS", "MS", "Fratio", "pvalue"};
ANOVA = DF || SS || MS || Fratio || Pvalue;
print ANOVA [rowname = factors colname =Col label="Factoraial ANOVA" format=10.03];

I defined the columns names first. I have used || to concatenate the columns and saved it as ANOVA. Finally in the print stage I have inserted the factor as rowname and specified the the format. 

Even though I don't need every single column to be formatted in the format I used but I wasn't able to figure out how to use the method in the article you suggested.

[DJ20]

I have attached the program and 2 different sets of data with it.

It would be great if you check it.

Any suggestion would be greatly appreciated.

Thanks,

DJ

[IanWakeling]

I am sorry, I don't have the time to check your program as it is quite large.  What I suggest you do is validate what you have done in IML against a standard.  You could export the design variables A B C D and response to a SAS dataset and then fit the model with main effects and 2-way interactions using PROC GLM.

Best,

Ian

[DJ20]

Thanks for your help @IanWakeling 

BTW I only wanted you to run the program to see how the table is constructed because I felt I didn't explain the situation correctly. I am not expecting you to waste you time checking my work. 

In terms of the calculations everything is correct. The only thing I was trying to improve is the look of the final table however there is no harm of checking it using PROC GLM. 

Thank you very much.

DJ

[IanWakeling]

I misunderstood what you were asking for.  Looking at the last ANOVA table,  things would be improved if you used rowcatc(CLC) instead of rowcat(CLC).  The extra 'c' will compress the blanks and make things look more tidy.   As you probably want different formats for different columns, it might be better not to concatenate everything into one matrix, so switch to something similar to the 2nd suggestion that I made earlier:

print 'ANOVA table',CLC [format=$10.] DF [format=3.0] SS [format=8.3] MS [format=8.3] Fratio [format=6.2] pvalue [format=pvalue6.4];

[Rick_SAS]

If your goal is to print both character and numeric values in a single table, to use different formats for each column, and to provide a label for the table, then you can put the data into a SAS/IML table, assign formats to the variables, and use the TablePrint subroutine to display it. For an overview and examples, see the article, "Print tables in SAS/IM."

You can even the TEMPLATE= option to specify the same ODS template that the SAS regression procedures use to display the ANOVA tables. See the "Advanced Printing" section in the same article. 

Tables were introduced in SAS/IML14.2 (released with SAS 9.4m4). For more about SAS/IML tables and other data structures, see the paper "More than Matrices" (Wicklin, 2017)

[DJ20]

Hi 

I am a bit confused. I have been trying to use the TablePrint Subroutine however I couldn't because I have my data in separate columns!!

I have read the article few times but I couldn't figure out how to get my data in the right format before I print it in table using the function you suggested.

Basically, I have the following columns which I need to print in a table together:

factors (character)

DF (degrees of freedom)(numeric)

SS (sum of squares)(numeric)

MS (mean square)(numeric)

F-ratio(numeric)

p_value(numeric)

Can you please guide me.

Thanks

DJ

[WeiChen]

dr rick's links show hwo to use table* functions in doc to create and add vars to table and set formats. Like tablecreate to ceate table and tablesetformat to set formats.

[DJ20]

hi @WeiChen 

I am using SAS 9.4 and it looks like its an old version because I tried the table* functions before but they didn't work giving me lots of error messages.

thanks for your reply anyway.

DJ

[Rick_SAS]

As Ian says, it is your responsibility to check or validate the program. Compare it to problems on which the answer is known (like from a textbook).

In answer to your latest question, here is how to create an ANOVA table and set the formats on three of the columns:

proc iml;

factors = {"Intercept", "X1", "X2"};
DF = {1, 1, 1};
SS = {123, 456, 789};
MS = {12, 34, 56};
Fratio = {2.1, 3.2, 4.3};
pvalue= {0.1, 0.01, 0.00001};

T = TableCreate("factors", factors);
call TableAddVar(T, {"DF" "SS" "MS" "Fratio" "pvalue"},
                      DF || SS || MS || Fratio ||pvalue);
call TableSetVarFormat(T, {"DF" "Fratio" "pvalue"}, {"BEST3." "9.4" "pvalue6.4"});
call TablePrint(T);

[DJ20]

Hi @Rick_SAS 

Thanks for your reply.

Its giving me the following errors

ERROR: Invocation of unresolved module TABLECREATE.

ERROR: Invocation of unresolved module TABLEADDVAR.

ERROR: Invocation of unresolved module TABLESETVARFORMAT.

ERROR: Invocation of unresolved module TABLEPRINT.

does that mean I am using an old version??

AS for the program, obviously I wasn't expecting you to use your own time to check my own work.. I only wanted you to run it to be able to see the output of the table because I wasn't able to upload a picture of the output here. 

your help is greatly appreciated. Thanks a lot,

DJ

[Rick_SAS]

Yes, it means you are using an old version. As I said in an earlier post, "Tables were introduced in SAS/IML14.2 (released with SAS 9.4m4)."  That version of SAS was released in 2016.

If I remember correctly, you also do not have lists. Lists and tables are big features.

Are you running SAS through a company? A University? If your work is important, you might encourage your company to upgrade to SAS/IML 15.1 (SAS 9.4m6).