BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zana
Calcite | Level 5

Hello,

Currently i have problem for convert some submatrix from an output solution file. The one of output table (this output file contains 4000 table like as follows) is represented as the follows (7*17):


Please let me i show that as a simple way.


1234567
891011121314
15161718192021
22232425262728
29303132333435
36373839404142
43444546474849
50515253545556
57585960616263
64656667686970
71727374757677
78798081828384
85868788899091
92939495969798
99100101102103104105
106107108109110111112
113114115116117118.

Now, i would like to create three submatrix. But this is more important, these submatrix could have a different dimensions. For example, maybe the first submatrix have 8*8, the 2nd one 4*4 and the 3rd one have 5*5 (or any other modes).

1st submatrix;

12345678
29101112131415
310161718192021
411172223242526
512182327282930
613192428313233
714202529323435
815212630333536

2nd submatrix;

37383940
38414243
39424445
40434546


3rd submatrix;

4748495051
4852535455
4953565758
5054575960
5155586061


How could i do it?


Many thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

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

View solution in original post

24 REPLIES 24
Ksharp
Super User

That is not submatrix. That is creating a whole new matrix . The parent matrix  only has 17*7 . How can you subset it to be 8*8 ?

CTorres
Quartz | Level 8

Zana,

I think you have to better define the problem to solve:

Having an initial matriz n*m, how do you determine how many submatrixes you need and the dimensions of them?

Please explain.

CTorres

zana
Calcite | Level 5

Dear all thanks for all your reply.

I'm so sorry for delay getting back to you. My request was a little confusing, so i decide to discuss step by step. However, I confused too.

Overall, in some of these submatrix (presented above) included other submatrix (e.g., the submatrix 4*4 had 4 matrix 2*2; this is 2 matrix 2*2 about submatrix 4*4). I think that after extract these main submatrix (8*8, 4*4 & 5*5) maybe it will not so difficult for extracting submatrix in each of these main submatix.

However, it will so well if we could extract these submatix directly.



For clarity, overall my request is as follows:

My table:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

.

1st submatrix (1st main_submatrix);                                                         

1

2

3

4

5

6

7

8

2

9

10

11

12

13

14

15

3

10

16

17

18

19

20

21

4

11

17

22

23

24

25

26

5

12

18

23

27

28

29

30

6

13

19

24

28

31

32

33

7

14

20

25

29

32

34

35

8

15

21

26

30

33

35

36

1st submatrix (this can be extracted from 1st main_submatrix [8*8] or directly from my table).

1

5

5

27

2nd submatrix ....

9

13

13

31

3rd submatrix ...

16

20

20

34

4st submatrix ...

22

26

26

36

About 2nd main_submatrix the included submatrix should be like as follows (this can also be extracted from 2nd main_submatrix [4*4] or directly from my table) :

1st ..

37

39

39

44

2nd ..

41

43

43

46

And the last main_submatrix (5*5) do not have any submatrix.

Thank you so much for your kind.

Rick_SAS
SAS Super FREQ

You can use subscripts to extract the submatrices:

S1 = M1[{1 5}, {1 5}];

S2 = M1[{2 6}, {2 6}];

S3 = M1[{3 7}, {3 7}];

etc.

Of course, you could put this logic into a loop.

Good luck!

Rick_SAS
SAS Super FREQ

I don't fully understand how you are creating these matrices. It looks like the first matrix is the elements 1:36 packed into a symmetric matrix. The second is 37:46, packed into a symmetric matrix, and the last is 47:61.

Matrices in IML are stored rowwise. You can use subscripts to extract submatrices. The following statements extract the elements from the big matrix and use the SQRVECH function to pack those elements into a symmetric matrix:

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

M = A[1:36];   /* extract elements 1:36 */
M1 = sqrvech(M);
print M1;

M = A[37:46];   /* extract elements 37:46 */
M2 = sqrvech(M);
print M2;

zana
Calcite | Level 5

Dear Rick, thanks for you. I should inform you that this table only is a simple example (my output file is't like that). Please see the original output file (Meanwhile i used SAS9.1.3):

   2.826      0.1117      0.5655E-01 -0.2051     -0.9951     -0.3791E-01 -0.2002E-01

-0.7701E-01  0.4744E-02  0.2165E-02 -0.8538E-02 -0.3539E-01 -0.1632E-02 -0.6954E-03

-0.4663E-02  0.1381E-02 -0.1599E-02 -0.2514E-01 -0.9058E-03 -0.4986E-03  0.5249E-03

  0.4369E-01 -0.3276E-02 -0.2731E-03 -0.6431E-03  0.2844E-01   1.032      0.2738E-01

  0.2465E-01 -0.4330E-01  0.1349E-02  0.7079E-03 -0.1114E-02  0.8279E-03 -0.1147E-02

  0.2654E-01   11.59      0.2370      0.2432      0.1908       1.796      0.4569E-01

  0.5911E-01 -0.2752      0.1163E-01  0.6655E-02  0.1226E-01  0.1842E-01  0.1013E-02

  0.1045E-02 -0.5404E-02  0.7722E-02  0.8686E-02  0.4204E-01  0.1071E-02  0.1562E-02

-0.4825E-02  0.1291     -0.7895E-01 -0.3190E-02 -0.1356E-02  0.9782E-02   1.391   

  0.3897E-01  0.4140E-01 -0.1326      0.1479E-02  0.1124E-02 -0.3741E-02  0.1285E-02

-0.3616E-02  0.2953E-01   20.56      0.5560      0.5731     -0.6843      0.7305   

  0.2406E-01  0.4680E-01  0.3018E-01  0.1988E-01  0.1599E-01 -0.2887E-01  0.2563E-01

-0.4890E-03  0.1693E-02 -0.3787E-02  0.1610E-01 -0.1922E-01  0.2705E-01  0.6995E-03

  0.1439E-02 -0.2149E-02  0.6893     -0.2031     -0.8243E-02 -0.7854E-02  0.8168E-01

   4.398      0.1241      0.1144     -0.1733      0.4284E-02  0.3585E-02 -0.5150E-02

  0.3513E-02 -0.3947E-02  0.1259       12.93      0.3446      0.3637     -0.4216   

  0.4785E-01  0.1194E-01  0.1851E-01  0.1536E-01 -0.3895E-02   1.157

Best regards

Rick_SAS
SAS Super FREQ

You should know that SAS 9.1.3 is more than 10 years old. See How old is your version of SAS? Release dates for SAS software - The DO Loop

CTorres
Quartz | Level 8

Zana,

The following macro includes the logic to generate the submatrixes:

593  %macro Submatrix(initval,dim);
594  data _Null_;
595    array SubM(&dim,&dim);
596    n=&initval;
597    do i=1 to &dim;
598       do j=1 to &dim;
599          if i Le j then do;
600             SubM(i,j)=n;
601             n+1;
602          end;
603          else SubM(i,j)=SubM(j,i);
604       end;
605    end;
606    do i=1 to &dim;
607       do j=1 to &dim;
608          put SubM(i,j) 3.0 @;
609       end;
610       put;
611    end;
612  run;
613  %mend Submatrix;
614 %submatrix(1,8)

  1  2  3  4  5  6  7  8
  2  9 10 11 12 13 14 15
  3 10 16 17 18 19 20 21
  4 11 17 22 23 24 25 26
  5 12 18 23 27 28 29 30
  6 13 19 24 28 31 32 33
  7 14 20 25 29 32 34 35
  8 15 21 26 30 33 35 36
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


615  %submatrix(37,4)

37 38 39 40
38 41 42 43
39 42 44 45
40 43 45 46
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


616  %submatrix(47,5)

47 48 49 50 51
48 52 53 54 55
49 53 56 57 58
50 54 57 59 60
51 55 58 60 61
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

CTorres

zana
Calcite | Level 5

Dear Torres, so thanks. Please say me how can i run your macro code? (i used SAS 9.1.3)

Thank you.

CTorres
Quartz | Level 8

The following is the macro code. I am sure it will work in SAS 9.1.3:

%macro Submatrix(initval,dim);

data _Null_;

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

            n+1;

         end;

         else SubM(i,j)=SubM(j,i);

      end;

   end;

   do i=1 to &dim;

      do j=1 to &dim;

         put SubM(i,j) 3.0 @;

      end;

      put;

   end;

run;

%mend Submatrix;

The way to run the macro is:

%submatrix(1,8)

%submatrix(37,4)

%submatrix(47,5)

where the first argument is the initial value used to populate the matrix and the second is the dimension of the matrix.

You can include the macro (relevant logic in bold) in your programs.

CTorres

zana
Calcite | Level 5

Thanks Torres. The program for extract submatrix from my table (example) is true. But when i tried to run the program at the following, i can not found my expected output. Please see the program and outputs;

data have;

input v1-v7;

cards;

   2.826      0.1117      0.5655E-01 -0.2051     -0.9951     -0.3791E-01 -0.2002E-01

-0.7701E-01  0.4744E-02  0.2165E-02 -0.8538E-02 -0.3539E-01 -0.1632E-02 -0.6954E-03

-0.4663E-02  0.1381E-02 -0.1599E-02 -0.2514E-01 -0.9058E-03 -0.4986E-03  0.5249E-03

  0.4369E-01 -0.3276E-02 -0.2731E-03 -0.6431E-03  0.2844E-01   1.032      0.2738E-01

  0.2465E-01 -0.4330E-01  0.1349E-02  0.7079E-03 -0.1114E-02  0.8279E-03 -0.1147E-02

  0.2654E-01   11.59      0.2370      0.2432      0.1908       1.796      0.4569E-01

  0.5911E-01 -0.2752      0.1163E-01  0.6655E-02  0.1226E-01  0.1842E-01  0.1013E-02

  0.1045E-02 -0.5404E-02  0.7722E-02  0.8686E-02  0.4204E-01  0.1071E-02  0.1562E-02

-0.4825E-02  0.1291     -0.7895E-01 -0.3190E-02 -0.1356E-02  0.9782E-02   1.391  

  0.3897E-01  0.4140E-01 -0.1326      0.1479E-02  0.1124E-02 -0.3741E-02  0.1285E-02

-0.3616E-02  0.2953E-01   20.56      0.5560      0.5731     -0.6843      0.7305  

  0.2406E-01  0.4680E-01  0.3018E-01  0.1988E-01  0.1599E-01 -0.2887E-01  0.2563E-01

-0.4890E-03  0.1693E-02 -0.3787E-02  0.1610E-01 -0.1922E-01  0.2705E-01  0.6995E-03

  0.1439E-02 -0.2149E-02  0.6893     -0.2031     -0.8243E-02 -0.7854E-02  0.8168E-01

   4.398      0.1241      0.1144     -0.1733      0.4284E-02  0.3585E-02 -0.5150E-02

  0.3513E-02 -0.3947E-02  0.1259       12.93      0.3446      0.3637     -0.4216  

  0.4785E-01  0.1194E-01  0.1851E-01  0.1536E-01 -0.3895E-02   1.157 .

  ;

%macro Submatrix(initval,dim);

data have;

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

            n+1;

         end;

         else SubM(i,j)=SubM(j,i);

      end;

   end;

   do i=1 to &dim;

      do j=1 to &dim;

         put SubM(i,j) 3.0 @;

      end;

      put;

   end;

run;

%mend Submatrix;

%submatrix(1,8)

%submatrix(37,4)

%submatrix(47,5)

432  data have;

433  input v1-v7;

434  cards;

NOTE: The data set WORK.HAVE has 17 observations and 7 variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.01 seconds

452    ;

453  %macro Submatrix(initval,dim);

454  data have;

455     array SubM(&dim,&dim);

456     n=&initval;

457     do i=1 to &dim;

458        do j=1 to &dim;

459           if i Le j then do;

460              SubM(i,j)=n;

461              n+1;

462           end;

463           else SubM(i,j)=SubM(j,i);

464        end;

465     end;

466     do i=1 to &dim;

467        do j=1 to &dim;

468           put SubM(i,j) 3.0 @;

469        end;

470        put;

471     end;

472  run;

473  %mend Submatrix;

474

475  %submatrix(1,8)

  1  2  3  4  5  6  7  8

  2  9 10 11 12 13 14 15

  3 10 16 17 18 19 20 21

  4 11 17 22 23 24 25 26

  5 12 18 23 27 28 29 30

  6 13 19 24 28 31 32 33

  7 14 20 25 29 32 34 35

  8 15 21 26 30 33 35 36

NOTE: The data set WORK.HAVE has 1 observations and 67 variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

476  %submatrix(37,4)

37 38 39 40

38 41 42 43

39 42 44 45

40 43 45 46

NOTE: The data set WORK.HAVE has 1 observations and 19 variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

477  %submatrix(47,5)

47 48 49 50 51

48 52 53 54 55

49 53 56 57 58

50 54 57 59 60

51 55 58 60 61

NOTE: The data set WORK.HAVE has 1 observations and 28 variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

CTorres
Quartz | Level 8

Hi Zana,

Let me first say that I do not understand the "rules" to decide how many submatrixes to get (one?, two?, three?, four?), the diference between Submatrix? and Main_Submatrix? and the dimension of them (4*4?, 8*8?, 5*5?) based on the dimension of the source matrix (8*4 as in the originat request? or 17*7 as in the second request?). I am completly lost about what those numbers mean and what is the purpose of the submatrixes. (????) You did not explain enough.

What I think I understood looking at the sample square submatrices you provided (but maybe I am wrong) is how to fill the them with consecutive integer numbers starting at an initial value. I coded the algorithm in the Submatrix macro.

With that said, and if I understand what you need (I am not sure) I suggest the following approach to solve your problem:

1. Convert the source matrix in a vector: for example the matrix M(8,4) would become a vector V(32) and the matrix M(17*7) a vector V(119).
2. Using the relevant logic in the Submatrix macro obtain the indexes to use to fill the target submatrix
3. Use those indexes to get the number (in the vector) to fill each cell of the submatrix.
4. Output the submatrix in the format you want or need (SAS Table?, report?).

Program to perform Step 1:

data have;
input X1-X7;
cards;
   2.826      0.1117      0.5655E-01 -0.2051     -0.9951     -0.3791E-01 -0.2002E-01
-0.7701E-01  0.4744E-02  0.2165E-02 -0.8538E-02 -0.3539E-01 -0.1632E-02 -0.6954E-03
-0.4663E-02  0.1381E-02 -0.1599E-02 -0.2514E-01 -0.9058E-03 -0.4986E-03  0.5249E-03
  0.4369E-01 -0.3276E-02 -0.2731E-03 -0.6431E-03  0.2844E-01   1.032      0.2738E-01
  0.2465E-01 -0.4330E-01  0.1349E-02  0.7079E-03 -0.1114E-02  0.8279E-03 -0.1147E-02
  0.2654E-01   11.59      0.2370      0.2432      0.1908       1.796      0.4569E-01
  0.5911E-01 -0.2752      0.1163E-01  0.6655E-02  0.1226E-01  0.1842E-01  0.1013E-02
  0.1045E-02 -0.5404E-02  0.7722E-02  0.8686E-02  0.4204E-01  0.1071E-02  0.1562E-02
-0.4825E-02  0.1291     -0.7895E-01 -0.3190E-02 -0.1356E-02  0.9782E-02   1.391  
  0.3897E-01  0.4140E-01 -0.1326      0.1479E-02  0.1124E-02 -0.3741E-02  0.1285E-02
-0.3616E-02  0.2953E-01   20.56      0.5560      0.5731     -0.6843      0.7305  
  0.2406E-01  0.4680E-01  0.3018E-01  0.1988E-01  0.1599E-01 -0.2887E-01  0.2563E-01
-0.4890E-03  0.1693E-02 -0.3787E-02  0.1610E-01 -0.1922E-01  0.2705E-01  0.6995E-03
  0.1439E-02 -0.2149E-02  0.6893     -0.2031     -0.8243E-02 -0.7854E-02  0.8168E-01
   4.398      0.1241      0.1144     -0.1733      0.4284E-02  0.3585E-02 -0.5150E-02
  0.3513E-02 -0.3947E-02  0.1259       12.93      0.3446      0.3637     -0.4216  
  0.4785E-01  0.1194E-01  0.1851E-01  0.1536E-01 -0.3895E-02   1.157 .
  ;
run;
data vector (keep= V:);
  Array v(119);
  array x(7);
  i=0;
  do until (eof);
    set have end=eof;
    do j=1 to 7;
       v(i*7+j)=x(j);
    end;
    i=i+1;
  end;
  output;
  stop;
run;

Modified Submatix macro to perform steps 2 to 4 creating three SAS Tables: Subm_1_8, Subm_37_4 and Subm_47_5:

%macro Submatrix(initval,dim,vectdim);
data SUBM_&initval._&dim (keep=var:);
   array var(&dim);
   array v(&vectdim);
   Set vector;
   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);
            n+1;
         end;
         else SubM(i,j)=SubM(j,i);
      end;
   end;
   do i=1 to &dim;
      do j=1 to &dim;
         var(j)=SubM(i,j);
      end;
      output;
   end;
run;
%mend Submatrix;

%submatrix(1,8,119)
%submatrix(37,4,119)
%submatrix(47,5,119)

I hope this helps,

CTorres

Rick_SAS
SAS Super FREQ

And please clarify whether you want the solution in SAS/IML or in the DATA step. I believe that I have provided a solution in the SAS/IML language.

zana
Calcite | Level 5

ِِDear Rick, thank you so much. CTorres program was suitable, but as i had said my output file contain more tables (near 4000 table) so i think for solve that i need a loop program. However please let me i have your idea by SAS/IML.

Best regards

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!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 24 replies
  • 2659 views
  • 6 likes
  • 5 in conversation