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.
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;
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;
Thanks! It works perfectly.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.