topic Re: How can I Concatenate more than two columns with different variables? in SAS/IML Software and Matrix Computations

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

DJ20 — Thu, 05 Mar 2020 23:24:40 GMT

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,

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

Reeza — Fri, 06 Mar 2020 01:45:56 GMT

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

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

IanWakeling — Fri, 06 Mar 2020 09:06:55 GMT

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.

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

Rick_SAS — Fri, 06 Mar 2020 10:45:20 GMT

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)

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

DJ20 — Mon, 09 Mar 2020 22:08:22 GMT

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

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

DJ20 — Mon, 09 Mar 2020 22:12:43 GMT

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.

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

DJ20 — Mon, 09 Mar 2020 22:17:28 GMT

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,

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

WeiChen — Mon, 09 Mar 2020 23:29:13 GMT

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.

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

IanWakeling — Tue, 10 Mar 2020 08:31:42 GMT

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

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

Rick_SAS — Tue, 10 Mar 2020 10:13:52 GMT

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);

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

DJ20 — Tue, 10 Mar 2020 10:36:11 GMT

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,

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

DJ20 — Tue, 10 Mar 2020 10:41:57 GMT

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.

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

Rick_SAS — Tue, 10 Mar 2020 10:45:45 GMT

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

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

DJ20 — Tue, 10 Mar 2020 10:46:41 GMT

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.

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

DJ20 — Tue, 10 Mar 2020 10:55:12 GMT

Thank you very much.

Yes I am using it through a company. I will suggest that SAS should be upgraded.

Thank you so much for you time and help.

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

IanWakeling — Tue, 10 Mar 2020 17:09:42 GMT

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];