How to extract submatrix?

[CTorres]

I do not understand when you say "my output file contain more tables". It is very difficult (at least for me) to help you because I do not understand the problem. Sorry.

[zana]

Dear Torres, so thanks for your kinds.

I'm so sorry for taking your time. I'm sending the output file (contain 3600 different table, all of them had 7 columns*17 rows). Please see it and let me i have your valuable ideas for solve that.

Best regards

[CTorres]

Hi Zana,

I could not open the attached file.

[zana]

Hi Torres,

You could't open that by TextPad, Notepad or other software? OK, for simplicity this time i sent you as Excel file. But i should inform that in this case (as excel file) you can find 200 row, where each row considered as a table (in other word i changed each table into a row). Regardless the first column (where determine the number of rows), each table (here row) has 118 columns.

Best regards

Zana

[CTorres]

Hi Zana,

I couldn't read this file either. Please attach an uncompressed excel file with the first 10 rows only. This would be enough to test the program.

[zana]

I'm so sorry for delay getting back to you. Now you can found the first 10 rows.

Best regards

Zana

[CTorres · Accepted solution]

Hi Zana,

I have made a few modifications to the Submatrix macro so that it now uses one more parameter (Row) which is the record number of the SAS Dataset (VECTOR) created from the "Output_fil" excel file once converted to .csv format.
I have also created the Process macro to loop from 1 to n rows of the vector dataset. You can change this macro to process just the desired rows.

Be aware that the process creates three SAS datasets for every row in the vector dataset!.

The following is the SAS Code:

1. SAS Program to create the Vector dataset:
data vector;
    infile 'P:\Documents\Output_Fil.csv' delimiter = ',' MISSOVER DSD lrecl=32767 ;
    input Row V1-V118;
    V119=0;
run;

2. Submatix Macro:
%macro Submatrix(initval,dim,vectdim,row);
data SUBM_&row._&initval._&dim (keep=var:);
   array var[&dim];
   array v[&vectdim];
   p=&row;
   Set vector(drop=row) point=p;
   array SubM[&dim,&dim];
   n=&initval;
   do i=1 to &dim;
      do j=1 to &dim;
         if i Le j then do;
            SubM[i,j]=v;
            n+1;
         end;
         else SubM[i,j]=SubM[j,i];
      end;
   end;
   do i=1 to &dim;
      do j=1 to &dim;
         var=SubM[i,j];
      end;
      output;
   end;
   stop;
run;
%mend Submatrix;

3. Process macro:
%macro process;
  proc sql noprint;
    select count(*) into :nobs
    from work.vector;
  quit;
  %do i=1 %to &nobs;
     %submatrix(1,8,119,&i);
     %submatrix(37,4,119,&i);
     %submatrix(47,5,119,&i);
  %end;
%mend;

4. Execution:
%process

[zana]

So thanks Torres!  That is what I wanted

Regards

Zana

[Rick_SAS]

Here it is in SAS/IML:

proc iml;
start Submatrix(A, initval,dim);
   L = dim*(dim+1)/2;
   k = initval:(initval+L-1);
   return( sqrvech(A) );
finish;

A = shape(1:(7*17), 7);  /* create 7 x 17 example */

M1 = Submatrix(A, 1, 8);
print M1;
M2 = Submatrix(A, 37, 4);
print M2;
M3 = Submatrix(A, 47, 5);
print M3;

[art297]

Zana,

I have no idea what your data represent, but the file appears to contain 3600 rows with each containing an id, number of variables (which appears to be a constant 118) and 118 values. Does the following extract the data as desired?

data matricies (drop=i j k);

  infile "c:\temp\The output File" termstr=LF lrecl=320000000;

  informat varid varnum 8.;

  array thedata(118) data1-data118;

  retain data:;

  call missing(of data1-data118);

  input varid varnum;

  j=0;

  do i=1 to ceil(varnum/7);

    do k=1 to 7;

      j+1;

      if j le varnum then do;

        input thedata(j) @@;

      end;

    end;

  end;

  input;

  output;

run;