title: transpose efficiently with proc sql
body: <p>i would like to know if it is possible to transpose efficiently from wide to long  using proc sql in sas.</p>
  
  <p>I'm aware that proc transpose is much quicker that the method i suggest below. But one of my objective would be to avoid storing the transposed table. </p>
  
  <p>Let's say for example, that i have table1 as </p>
  
  <pre><code>Id|   A|   B|   C|  D    
  _____________________
   1|  100|3500|6900| 10300
   2|  200| 250| 300| 350
   3|  150|  32| 400| 204
   4|  200| 800|1400| 2000
  </code></pre>
  
  <p>and i want to turn it into</p>
  
  <pre><code>id|col1|  col2|
  ______________
   1|   A|   100|
   1|   B|  3500|
   1|   C|  6900|
   1|   D| 10300|
   2|   A|   200|
   2|   B|   250|
   2|   C|   300|
   2|   D|   350|
   3|   A|   150|
   3|   B|    32|
   3|   C|   400|
   3|   D|   204|
   4|   A|   200|
   4|   B|   800|
   4|   C|  1400|
   4|   D|  2000|
  </code></pre>
  
  <p>I could do this;</p>
  
  <p>select id, 'A' as col1, A as col2 <br/>
   from table1 <br/>
   where A ~="" <br/>
  union
   select id, 'B' as col1, B as col2 <br/>
   from table1 <br/>
   where B ~="" <br/>
  etc </p>
  
  <p>but it is highly inefficient.</p>
  
  <p>Any idea? Thanks.</p>