Hello, I'm trying to transform a table into two tables that are divided by the diagonal line of the original table. My idea is to first remove the data above and below the diagonal line (or turn them into missing value), then the next step is to shift the remaining value to either the right or left. I have tried my best to describe it in the excel file attached. Thank you for your help!
1. Please format your code by using the appropriate icon when pasting
2. Many people cannot/will not open MS Office files downloaded from the web, so i did not see what's there
3. Like this?
data ABOVE
BELOW;
set HAVE;
array COLS [*] A--Q;
do ITER=1 to dim(COLS);
if ITER>_N_ then COLS[ITER]=.;
end;
output BELOW;
set HAVE;
do ITER=1 to dim(COLS);
if ITER=<_N_ then COLS[ITER]=.;
end;
output ABOVE;
run;
1. Transpose your data to a long format, so that you have Start, Stop, Measure
2. If Start = Stop that's the diagnoal, Start > Stop is the upper diagonal, Stop > Start is the bottom diagonal
3. Re-arrange as desired using PROC TRANSPOSE again
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd
If you post data as a data step, I can try and code something roughly.
Thank you for your response. I hope the data code here could be of help:
data have;
input a b c d e f g h i j k l m n o p q;
datalines;
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 67 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 119
120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187
188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204
205 506 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
;
run;
Use an array:
data have;
length from $8;
array to {*} A B C D E F G H I J K L M N O P Q;
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;
data want;
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;
proc print data=want noobs; run;
Hi @PGStats ,
Thank you. After using your code and modifying it a bit, I was able to create 2 tables I like how I wanted.
However, after doing work on those 2 tables, now I need to combine them back to get the original table. Could you please help me on how to get this done?
Here is the code I'm currently using and its result. Now I need to combine table 2 and 3 back to table 1.
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;
format u1-u16 percent10.4;
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;
format d1-d16 percent10.4;
run;
proc print data = downgrade noobs; run;
1. Please format your code by using the appropriate icon when pasting
2. Many people cannot/will not open MS Office files downloaded from the web, so i did not see what's there
3. Like this?
data ABOVE
BELOW;
set HAVE;
array COLS [*] A--Q;
do ITER=1 to dim(COLS);
if ITER>_N_ then COLS[ITER]=.;
end;
output BELOW;
set HAVE;
do ITER=1 to dim(COLS);
if ITER=<_N_ then COLS[ITER]=.;
end;
output ABOVE;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: