topic Separate a table by its diagonal line and then combine them back in SAS Programming

Separate a table by its diagonal line and then combine them back

Minh2710 — Sat, 27 Feb 2021 02:28:40 GMT

Hello everyone.

My task is to split a table into two by its diagonal line. The values in the two tables must then be shifted to the left and one of the table must be flipped horizontally before doing that. With the help of members of this community, I was able to achieve that by the following lines of code:

data have;
length from $8;
array to {*} r1 r2 r3 r4 r5 r6 r7 r8 r9 r10 r11 r12 r13 r14 r15 r16 r17;

do _i = 1 to dim(to);
    from = vname(to{_i});
    do _j = 1 to dim(to);
        _x + 1;
        to{_j} = _x;
        end;
    output;
    end;
drop _: ;
format _numeric_ 3.0;
run;

proc print data = have noobs; run;

/*Separate upgrade notch*/
data below;
set have;
array to{*} _numeric_;
_i = _n_;
do _j = _i to 1 by -1;
    to{dim(to)+_j-_i} = to{_j};
    end;
do _j = dim(to)-_i to 1 by -1;
    call missing(to{_j});
    end;
drop _: ;
run;

data upgrade (drop = r1-r17 i);
	set below;
	array u(*) u1-u16;
	array r(*) r1-r17;
	do i = 1 to 16;
		u(0 + i) = r(16 + 1 - i);
	end;
run;

proc print data = upgrade noobs; run;

/*Separate downgrade notch*/
data above;
  set have;
  array COLS [*] r1 - r17;
  do ITER=1 to dim(COLS);
    if ITER=<_N_ then COLS[ITER]=.;
  end;
  output above;
  drop ITER;
run;

data downgrade (drop=r1-r17 j i);
   set above;
   array in r1-r17;
   array out d1-d17;
   j=1;
   do i=1 to 17;
      if in(i) ne ' ' then do;
         out(j)=in(i);
         j+1;
      end;
   end;
drop d17;
run;

proc print data = downgrade noobs; run;

The result is as follow:

Original table:

Resulting tables:

Now after I have done all the work with table 2 and 3, I need to combine them back to Table 1 in the exact original order.

If anyone could provide an answer or hint, I would be very much appreciated.

Thank you.

Re: Separate a table by its diagonal line and then combine them back

Ksharp — Sat, 27 Feb 2021 11:42:23 GMT

It is IML thing. If you have SAS/IML .

data have;
length from $8;
array to {*} r1 r2 r3 r4 r5 r6 r7 r8 r9 r10 r11 r12 r13 r14 r15 r16 r17;

do _i = 1 to dim(to);
    from = vname(to{_i});
    do _j = 1 to dim(to);
        _x + 1;
        to{_j} = _x;
        end;
    output;
    end;
drop _: ;
format _numeric_ 3.0;
run;
/*Separate upgrade notch*/
data below;
set have;
array to{*} _numeric_;
_i = _n_;
do _j = _i to 1 by -1;
    to{dim(to)+_j-_i} = to{_j};
    end;
do _j = dim(to)-_i to 1 by -1;
    call missing(to{_j});
    end;
drop _: ;
run;
data upgrade (drop = r1-r17 i);
	set below;
	array u(*) u1-u16;
	array r(*) r1-r17;
	do i = 1 to 16;
		u(0 + i) = r(16 + 1 - i);
	end;
run;
/*Separate downgrade notch*/
data above;
  set have;
  array COLS [*] r1 - r17;
  do ITER=1 to dim(COLS);
    if ITER=<_N_ then COLS[ITER]=.;
  end;
  output above;
  drop ITER;
run;

data downgrade (drop=r1-r17 j i);
   set above;
   array in r1-r17;
   array out d1-d17;
   j=1;
   do i=1 to 17;
      if in(i) ne ' ' then do;
         out(j)=in(i);
         j+1;
      end;
   end;
drop d17;
run;








proc iml;
use  upgrade;
read all var _num_ into up[r=from c=vname];
close;
use  downgrade;
read all var _num_ into down;
close;
want=j(nrow(down),ncol(down)+1,.);

id1=loc(row(want)<col(want));
id2=loc(row(down)+col(down)<=ncol(down)+1);
want[id1]=down[id2];

up=up[,ncol(up):1];
id3=loc(row(want)>col(want));
id4=loc(row(up)+col(up)>ncol(up)+1);
want[id3]=up[id4];

mattrib want r=from l='';
print want;

/*create a output dataset*/
create want from want[r=from];
append from want[r=from];
close;
quit;

Re: Separate a table by its diagonal line and then combine them back

Minh2710 — Sat, 27 Feb 2021 19:59:51 GMT

Thanks! It works perfectly.