求助，资料转置问题，求高手指教，感激不盡!!!!!!!!!

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

请问怎样可以把资料横转直。并分2个表。

原数据:

Name	Tom (M)	Mary (F)	Peter (M)	Ane (F)	Susan (F)	Sam (M)
Number	1	2	3	4	5	6
Math	65	85	70	75	60	55
Eng	70	67	56	45	88	78
Chinese	80	75	50	69	77	77

结果:

Name	Code	Subject	Score
Tom (M)	1	Math	65
Tom (M)	1	Eng	70
Tom (M)	1	Chinese	80
Peter (M)	3	Math	70
Peter (M)	3	Eng	56
Peter (M)	3	Chinese	50
Sam (M)	6	Math	60
Sam (M)	6	Eng	88
Sam (M)	6	Chinese	77

Name	Code	Subject	Score
Mary (F)	2	Math	85
Mary (F)	2	Eng	67
Mary (F)	2	Chinese	75
Ane (F)	4	Math	75
Ane (F)	4	Eng	45
Ane (F)	4	Chinese	69
Susan (F)	5	Math	60
Susan (F)	5	Eng	88
Susan (F)	5	Chinese	77

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：求高手 subjects Chinese Subject Number 资料

proc transpose. 然后按条件筛选成2个datasets

proc import out=ex
datafile="E:\aaa.xlsx"
replace;
run;

proc transpose data=ex out=ex1(drop=_name_ rename=(_label_=name));
id name;
var Tom__M_--Sam__M_;
run;

proc transpose data=ex1 out=ex2(rename=(number=code _name_=subject col1=score));
by name number notsorted;
var math--chinese;
run;

data M F;
set ex2;
label name= subject=;
if index(name,'(M)') then output M;
else if index(name,'(F)') then output F;
run;

data table;
input a1 $ a2 $ a3 $ a4 $ a5 $ a6 $ a7;
cards;
Number 1 2 3 4 5 6
Math 65 85 70 75 60 55
Eng 70 67 56 45 88 78
Chinese 80 75 50 69 77 77
;
run;
proc sort data=table ;
by a1;
run;

proc transpose data=table out=test1(drop=_NAME_);
by a1;
var a2-a7;
run;

data score name;
set test1;
if a1='Number' then output name;
else output score;
run;

proc sql noprint;
select count(*) into :n from name;
quit;

data result;
set score(rename=(a1=Subject col1=Score));
Code=mod(_n_,&n.);
if Code=0 then Code=&n.;
run;

proc sort data=result;
by code descending Subject;
run;

data result1 result2;
set result;
if mod(Code,2) then output result1;
else output result2;
run;
你Name与Number是一一对应的，最后自己赋值就可以了。

ziyenano 发表于 2013-1-30 16:00
proc import out=ex
datafile="E:\aaa.xlsx"
replace;

感激不盡!!!!!!!!!
分开2次做transpose。我真笨。這也想不到。