Create matrices based on a reference table and separate data table sas iml

Question

I have a parametrization table that mentions whether the (i,j) th element of "matrix 1" is zero, residual of the row sum or has to be read from the data table. I also have a data table with all the values for different segments. How do I construct the matrix?

For example, let's say "param_table" is the parametrization table:

data param_table;
infile datalines dsd;
length FieldName $20 FieldSourceTable $20;
input Matrix_Id Column_Order Row_Order IsZero IsRowResidual IsColumnResidual FieldName FieldSourceTable;
datalines;
1,    1,   1,   0,  1,  0,  .,      .
1,    1,   2,   0,  0,  0,  xyz,    table1
1,    1,   3,   0,  0,  0,  abc,    table1
1,    2,   1,   1,  0,  0,  .,      .
1,    2,   2,   0,  0,  0,  pqr,    table1
1,    2,   3,   0,  0,  0,  mno,    table1
1,    3,   1,   0,  0,  0,  ab,     table1
1,    3,   2,   0,  0,  0,  pq,     table1
1,    3,   3,   0,  1,  0,  .,      .
;

"table 1" is the actual data containing the values and references from earlier table:

data table1;
input Year (country method Segment) ( : $12.)
  ABC XYZ PQR MNO AB PQ;
datalines;
2017 France ABC Retail    0.2 0.5 0.4 0.3 0.6 0.1
2017 France XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2
;
run;

How do I create matrices with these rules for each row (each key set) in table 1? For example, matrix for row 1 of "table1" would be:

(1-ab) 0    ab
xyz    pqr  pq
abc    mno  (1-abc-mno)

(1,1)th and (3,3) th elements are row residuals, therefore are (1 - sum of rest of the row), whereas (1,2)th element is 0:

0.4  0    0.6
0.5  0.4  0.1
0.1  0.3  0.6

I have added the data steps for file "param_table" which contains the references (the column names) and if it is zero or row residual. Also added the "table1" file which contains the actual values. For each row of "table1" we should have a matrix based on the rules mentioned in param_table.

Thanks!

Answer 1

Each matrix defined in param_table will correspond to a 2-D array associated with each row in table1. Suppose you have macro matrixfier that generates the source code statements needed to map from the table1 data into a specified array (i.e. matrix).

%macro matrixfier (matrix_id=1, arrayName=x, out=);
  %local rowCount colCount source z i; 
  %local s1 s2 s3 s4 s addr;

The macro will have to examine the parameter data to determine if the settings it contains are rational with regards to code generation.

  proc sql noprint;
    select *
    from PARAM_TABLE where matrix_id = &matrix_id
    and ( iszero not in (0,1) or
          isrowresidual not in (0,1) or
          iscolumnresidual not in (0,1) or
          sum(iszero,isrowresidual,iscolumnresidual) not in (0,1)
    );

    %if &sqlobs %then %do;
      %put ERROR: Parameters for matrix_id=&matrix_id. rejected for is* settings.;
      %abort cancel;
    %end;
    select max(z) as z into :z from
    ( select column_order, sum(iscolumnresidual) as z
      from PARAM_TABLE where matrix_id = &matrix_id
      and iscolumnresidual
      group by column_order
    );

    %if &z > 1 %then  %do;
      %put ERROR: Parameters for matrix_id=&matrix_id. rejected for iscolumnresidual settings.;
      %abort cancel;
    %end;

Determine how large the target array needs to be. Also, presume there is only one source table per matrix defined

    select max(column_order), max(row_order), max(fieldsourcetable) 
    into :colCount, :rowCount, :source
    from PARAM_TABLE where matrix_id = &matrix_id
    ;

Code generate DATA Step statements for assigning a value directly.

    select cats("&arrayName.(",row_order,',',column_order,')=', fieldname)
    into :s1 separated by ';'
    from PARAM_TABLE where matrix_id = &matrix_id
    and iszero=0 and isrowresidual=0 and iscolumnresidual=0
    order by row_order, column_order
    ;

Code generate DATA Step statements for assigning a zero value.

    select cats("&arrayName.(",row_order,',',column_order,')=0')
    into :s2 separated by ';'
    from PARAM_TABLE where matrix_id = &matrix_id
    and iszero
    order by row_order, column_order
    ;

Code generate DATA Step statements for computing row residuals.

    %do i = 1 %to &rowCount;
      select
        cats(B.row_order,',',B.column_order),
        '-' || A.fieldname
      into
        :addr,
        :s separated by ','
      from PARAM_TABLE A
      join PARAM_TABLE B
        on A.matrix_id = B.matrix_id
           and A.row_order = B.row_order
      where
        A.matrix_id = &matrix_id and A.row_order=&i 
        and A.isrowresidual=0 and A.iszero=0 and A.iscolumnresidual=0
        and B.isrowresidual=1
      ;

      %if &sqlobs > 0 %then %let s3=&s3&arrayName.(&addr)=sum(1,&s)%str(;);
    %end;

Code generate DATA Step statements for computing column residuals.

    %do i = 1 %to &colCount;
      select
        cats(B.row_order,',',B.column_order),
        '-' || A.fieldname
      into
        :addr,
        :s separated by ','
      from PARAM_TABLE A
      join PARAM_TABLE B
        on A.matrix_id = B.matrix_id
           and A.column_order = B.column_order
      where
        A.matrix_id = &matrix_id and A.column_order=&i 
        and A.isrowresidual=0 and A.iszero=0 and A.iscolumnresidual=0
        and B.iscolumnresidual=1
      ;

      %if &sqlobs > 0 %then %let s4=&s4&arrayName.(&addr)=sum(1,&s)%str(;);
    %end;

  quit;

Assemble the statements in a DATA Step.

  data &out;
    set &source;

    array &arrayName.(&rowCount,&colCount);

    call missing (of &arrayName.(*));

    * assignments;
    &s1;
    * zeroes;
    &s2;
    * row residuals;
    &s3;
    * column residuals;
    &s4;

    * log the matrix for this row;
    do _i = 1 to dim(&arrayName.,1);
      do _j = 1 to dim(&arrayName.,2);
        putlog &arrayName(_i,_j) 6.2 @;
      end;
      putlog;
    end;
    putlog;

  run;
%mend;

Resolve the parameters as applied to data

options mprint;
%matrixfier(matrix_id=1, arrayName=x, out=each);