Transform the observations above/below the diagonal line of a table and shift them left/right

[Minh2710]

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!

[Reeza]

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. 

[Minh2710]

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;

[Reeza]

That doesn't match your posted example...should I assume the first column will match the variable names in the spreadsheet?

[Minh2710]

Hi Reeza. Sorry, I think you can ignore the first column in the spreadsheet.

[Reeza]

No I can't, it defines the rules so if that doesn't hold with the FROM/TO then the logic won't hold. Implied positioning is never a good way to store data either.

[Minh2710]

Hi Reeza. I didn't know it was necessary. If that's the case then it is exactly what you assumed above that is the first column matches the variable name.

Here's the updated data step code:

data have;
input from_to $ a b c d e f g h i j k l m n o p q;
datalines;
a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
b 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
c 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
d 52 53 54 55 56 67 58 59 60 61 62 63 64 65 66 67 68
e 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
f 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
g 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
h 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
i 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
j 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
k 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187
l 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204
m 205 506 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221
n 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
o 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255
p 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272
q 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
;
run;

[PGStats]

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;

[Minh2710]

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;

[ChrisNZ · Accepted solution]

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;

[Minh2710]

Hi ChrisNZ. Thank you for your suggestion, I will pay attention to those points next time. About your code, it is exactly what I needed. Thank you very much.