如何使用 proc transpose 操作表格

问题描述

我有这张桌子:

ID   100  105 201 305 502 
100   .   0.2 0.3 0.2 0.2
200   0.1  0  0.4 0.5 0.1
201   0.2 0.1 0.4 0.3 0.1
304   .   .   0.2 0.2 0.3

我想要这样的东西:

Code ID   Val
100  100  .
100  200  0.1
100  201  0.2
100  304  .
105  100  0.2
105  200  0
105  201  0.1
105  304  .
...

我试过如下

proc transpose data=my_data out=test(rename=col1=Val);

run;

但输出与我预期的不同.我想我以错误的方式使用 proc transpose .我希望 Val 作为列而不是行.

But the output is different from what I would expect. I think I am using proc transpose in the wrong way. I would like to have Val as column not as a row.

你能就如何获得预期输出给我建议吗?

Can you give me suggestions on how to get the expected output?

推荐答案

我不知道你想用数值作为列名做什么，但它可以在一个 proc transpose 中实现.下面的代码输出了想要的结果:

I don't know what you are trying to do with numeric values as column names but it can be achieved in one proc transpose. The following code output the desired result:

data have;
infile datalines4 delimiter=",";
input ID "100"n "105"n "201"n "305"n "502"n ;
datalines4;
100,.,0.2,0.3,0.2,0.2
200,0.1,0,0.4,0.5,0.1
201,0.2,0.1,0.4,0.3,0.1
304,.,.,0.2,0.2,0.3
;;;;

proc transpose data=have out=stage1(rename=(_name_=Code col1=Val));
by id;
var "100"n "105"n "201"n "305"n "502"n;
run;

proc sort data=stage1 out=stage2;
by code id;
run;

data want;
retain code id val;
set stage2;
run;

结果(想要表):

[根据@LdM 评论添加]:

[Added based on @LdM comment]:

只需使用 dictionary.columns 表和 proc sql 来检索您想要的变量:

Just use the dictionary.columns table with proc sql to retrieve the variables that you want:

proc sql noprint;
select cats('"',name,'"','n') into :varnames separated by " "
from dictionary.columns
    where libname="WORK" and memname="HAVE" and name ne "ID";
quit;

proc transpose data=have out=stage1(rename=(_name_=Code col1=Val));
by id;
var &varnames.;
run;

这篇关于如何使用 proc transpose 操作表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！