使用 proc sql 高效转置 [英] transpose efficiently with proc sql

问题描述

我想知道是否可以在 sas 中使用 proc sql 有效地从宽转为长.

i would like to know if it is possible to transpose efficiently from wide to long using proc sql in sas.

我知道 proc transpose 比我在下面建议的方法要快得多.但我的目标之一是避免存储转置表.

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.

例如，我有 table1 作为

Let's say for example, that i have table1 as

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

我想把它变成

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|

我可以做到；

选择id，'A'为col1，A为col2
来自表 1
其中 A ~=""
联盟选择 id，'B' 作为 col1，B 作为 col2
来自表 1
其中 B ~=""
等

select id, 'A' as col1, A as col2
from table1
where A ~=""
union select id, 'B' as col1, B as col2
from table1
where B ~=""
etc

但效率极低.

有什么想法吗?谢谢.

推荐答案

如果您使用 SAS，请使用 PROC TRANSPOSE 作为此选项.在 PROC SQL; 中没有特别好的方法可以做到这一点，而许多 SQL 变体都有自己的数据透视方式，SAS 有 PROC TRANSPOSE 并希望您使用它.

If you're in SAS, use PROC TRANSPOSE for this option. There is no particularly good way to do this in PROC SQL; while many SQL variants have their own way to pivot data, SAS has PROC TRANSPOSE and expects you to use it.

SAS 数据步骤也非常有效地执行此操作，甚至可能比 PROC TRANSPOSE 更好.这是一个示例，包括创建注释中所述的视图.

The SAS datastep also does this very efficiently, perhaps even better than PROC TRANSPOSE. Here's an example, including creating a view as noted in the comments.

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

这篇关于使用 proc sql 高效转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！