sas - transpose efficiently with proc sql -

i know if possible transpose efficiently wide long using proc sql in sas.

i'm aware proc transpose quicker method suggest below. 1 of objective avoid storing transposed table.

let's example, have table1

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 

and want turn into

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| 

i this;

select id, 'a' col1, col2
table1
~=""
union select id, 'b' col1, b col2
table1
b ~=""
etc

but highly inefficient.

any idea? thanks.

if you're in sas, use proc transpose option. there no particularly way in proc sql; while many sql variants have own way pivot data, sas has proc transpose , expects use it.

the sas datastep efficiently, perhaps better proc transpose. here's example, including creating view noted in comments.

data want/view=want; set have; array vars b c d;                  *array of columns transpose; _t = 1 dim(vars);              *iterate on array (dim(vars) gives # of elements);   if not missing(vars[_t]) do;  *if current array element's value nonmissing;     col1=vname(vars[_t]);            *then store variable name array element in var;     col2=vars[_t];                   *and store value array element in var;     output;                          *and output new row;   end; end; drop b c d _t;                     *drop old vars (cols) , dummy variable _t; run;