# 加载中...

• 博客等级：
• 博客积分：0
• 博客访问：121,881
• 关注人气：199
• 获赠金笔：0支
• 赠出金笔：0支
• 荣誉徽章：

## 也说行列互换之proc transpose

(2011-10-16 15:12:36)

### 行列互换

data out指定源数据集和目标数据集，labelname为转置后SAS自动生成的变量（_label_, _name_）改名。Prefix ID联合控制目标数据集中的转置后生成的变量名。Var By联合控制转置后生成的“数据矩阵”， Var为要转置的变量，by 指定分组变量。

Topic１、转一个变量。Old1 转成new1

****solution1: proc transpose*****;

proc transpose data=old1 out=new1 (drop=_name_) prefix=date;

var date;

by name;

run;

*****solution2: do+array****;

data new1(drop=i date);

informat name ;

array dates[1:3]\$ date1-date3;

do i=1 to 3;

set old;

dates[i]=date;

end;

run;

proc sql noprint;

select max (countdate)

into:max

from (select count(date) as countdate from old1 group by name);

%let max=&max;

data new1(drop=i date);

informat name ;

array dates[&max]   \$ date1-date&max;

do i=1 to &max;

set old1;

by name;

dates[i]=date;

end;

run;

*** proc transpose***;

proc transpose data=new1 out=old1_back(drop=_name_ rename=(col1=date)) ;

var date1-date3;

by name;

run;

*****do +array****;

data old1_back(keep=name date);

set new1;

array dates[1:3] \$ date1-date3;

do i=1 to 3;

date=dates[i];

output;

end;

run;

Topic2 转多个变量。数据集old2 new2

Solution1: do+array

data new2(drop=i date result);

format name date1-date3 result1-result3;

array dates[1:3] \$  date1-date3;

array results[1:3] \$ result1-result3;

do i=1 to 3;

set old2;

dates[i]=date;

results[i]=result;

end;

run;

solution2: proc transpose +merge

转置多个变量时，无法一次性完成上述要求。需用merge做后继处理。

*****solution1plus: proc transpoe +merge***;

proc transpose data=old2 out=tmp;

var date result;

by name;

run;

data new2(drop=_name_);

merge tmp(where=(_name_='date')   rename=(col1-col3=date1-date3))

tmp(where=(_name_='result') rename=(col1-col3=result1-result3));

by name;

run;

或者一次转一个，转多次后MERGE.

proc transpose data=old2 out=tmp1(drop=_name_) prefix=date;

var date;

by name;

run;

proc transpose data=old2 out=tmp2(drop=_name_) prefix=result;

var result;

by name;

run;

data  new2;

merge tmp1 tmp2;

by name;

run;

如何转回来：

****do +array****;

data old2_back(keep=name date result);

set new2;

array dates[1:3] \$ date1-date3;

array results[1:3] \$ result1-result3;

do i=1 to 3;

date=dates[i];

result=results[i] ;

output;

end;

run;

***transpose +merge****;

proc transpose data=new2 out=old2_tmp;

var date1-date3 result1-result3;

by name;

run;

data old2_back(drop=_name_);

merge  old2_tmp(where=(substr(_name_,1,4)='date')    rename=(col1=date) )

old2_tmp(where=(substr(_name_,1,6)='result')  rename=(col1=result)) ;

by name;

run;

2.SUGI PAPER,Changing the Shape of Your Data:  PROC TRANSPOSE vs. Arrays

3.SAS 9.2 SQL Procedure User's Guide.

0

• 评论加载中，请稍候...

发评论

以上网友发言只代表其个人观点，不代表新浪网的观点或立场。

新浪BLOG意见反馈留言板　电话：4000520066 提示音后按1键（按当地市话标准计费）　欢迎批评指正

新浪公司 版权所有