如何得到这样的数据集

1. data aaa;
   
2. input site name$ g x1;
   
3. cards;
   
4. 1 a . 10
   
5. 1 b 1 11
   
6. 1 b 2 12
   
7. 1 c . 13
   
8. 2 a . 20
   
9. 2 b . 21
   
10. 2 c 1 22
    
11. 2 c 2 23
    
12. 4 a 1 30
    
13. 4 a 2 31
    
14. 4 b . 32
    
15. 4 c . 33
    
16. ;
    
17. run;

复制代码

1. data bbb;
   
2. input site x$;
   
3. cards;
   
4. 1 aa
   
5. 2 bb
   
6. 4 cc
   
7. ;
   
8. run;

复制代码

希望得到的数据集(变量为字符型)，同时site1 site2 site4三个变量的label为bbb数据集中对应的取值aa bb cc:
name    site1       site2          site4
a           10           20            30 31
b         11 12        21             32
c           13         22 23           33   


如何简单的编程实现，谢谢！

dxystata 发表于 2014-10-16 20:50
如果数据集aaa是这样，如何保持数据集中ccc中name取值的顺序为aaa数据中name的顺序c a b

如果每个site里的name取值的顺序都相同的话：
data ccc;
   set aaa end=last;
   by site name notsorted;

        if _n_=1 then call execute("data ddd(keep=name site: drop=site); merge ");
        if first.site then call execute(cats("ccc(where=(site=",site,") rename=(_x1=site",site,"))"));
        if last then call execute("; by name_order; run;");

        if first.site then name_order=0;
    if first.name then do; _x1=catx(" ",x1); name_order+1; end;
    else if not first.name then _x1=catx(" ",_x1,x1);
    if last.name;
    retain _x1 ;
run;

proc sql noprint;
   select distinct cats("site",site,"=",quote(strip(x))) into : label separated by " " from bbb;
quit;

proc datasets;
   modify ddd;
   label &label;
run;
quit;

proc sort data=aaa;
    by name site g;
run;

data ccc;
        set aaa;
        by name site g;
        if first.site then _x1=catx(" ",x1);
        if not first.site then _x1=catx(" ",_x1,x1);
        retain _x1;
        if last.site;
run;


proc transpose data=ccc out=ddd(drop=_:) prefix=site;
   by name;
   var _x1;
   id site;
run;


proc sql noprint;
   select distinct cats("site",site,"=",quote(strip(x))) into : label separated by " " from bbb;
quit;

proc datasets;
   modify ddd;
   label &label;
run;
quit;

修改标签部分与楼上相同，前半部分可以两次转置：
proc transpose data=aaa out=ccc;
var x1;
by name site;

run;
quit;

data ccc1(keep=name site c);
set ccc;
if col1^=. and col2^=. then c=cat(put(col1,z2.),"",put(col2,z2.));
else c=col1;
run;
proc transpose prefix=site data=ccc1 out=ccc2;
var c;
by name ;
id site;
run;
quit;

pobel 发表于 2014-10-16 16:22
proc sort data=aaa;
    by name site g;
run;

data ccc;
        set aaa;
        by name site g;
        if first.site then _x1=catx(" ",x1);
        if not first.site then _x1=catx(" ",_x1,x1);
        retain _x1;
        if last.site;
run;


proc transpose data=ccc out=ddd(drop=_:) prefix=site;
   by name;
   var _x1;
   id site;
run;


proc sql noprint;
   select distinct cats("site",site,"=",quote(strip(x))) into : label separated by " " from bbb;
quit;

proc datasets;
   modify ddd;
   label &label;
run;
quit;

1. data aaa;
   
2. input oid site name$ g x1;
   
3. cards;
   
4. 1 1 c . 10
   
5. 2 1 a 1 11
   
6. 3 1 a 2 12
   
7. 4 1 b . 13
   
8. 5 2 c . 20
   
9. 6 2 a . 21
   
10. 7 2 b 1 22
    
11. 8 2 b 2 23
    
12. 9 4 c 1 30
    
13. 10 4 c 2 31
    
14. 11 4 a . 32
    
15. 12 4 b . 33
    
16. ;
    
17. run;

复制代码

如果数据集aaa是这样，如何保持数据集中ccc中name取值的顺序为aaa数据中name的顺序c a b

magician1943 发表于 2014-10-16 16:46
修改标签部分与楼上相同，前半部分可以两次转置：
proc transpose data=aaa out=ccc;
var x1;
by name site;

run;
quit;

data ccc1(keep=name site c);
set ccc;
if col1^=. and col2^=. then c=cat(put(col1,z2.),"",put(col2,z2.));
else c=col1;
run;
proc transpose prefix=site data=ccc1 out=ccc2;
var c;
by name ;
id site;
run;
quit;

1. data aaa;
   
2. input oid site name$ g x1;
   
3. cards;
   
4. 1 1 c . 10
   
5. 2 1 a 1 11
   
6. 3 1 a 2 12
   
7. 4 1 b . 13
   
8. 5 2 c . 20
   
9. 6 2 a . 21
   
10. 7 2 b 1 22
    
11. 8 2 b 2 23
    
12. 9 4 c 1 30
    
13. 10 4 c 2 31
    
14. 11 4 a . 32
    
15. 12 4 b . 33
    
16. ;
    
17. run;

复制代码

如果数据集aaa是这样，如何保持数据集中ccc中name取值的顺序为aaa数据中name的顺序c a b

dxystata 发表于 2014-10-16 20:51
如果数据集aaa是这样，如何保持数据集中ccc中name取值的顺序为aaa数据中name的顺序c a b

Chang name='c' to other value, such as 'C', after sort by name, it will be sequence as C a b, at last step, change it back.

pobel 发表于 2014-10-16 16:22
proc sort data=aaa;
    by name site g;
run;

data ccc;
   set aaa end=last;
   by site name notsorted;

        if _n_=1 then call execute("data ddd(keep=name site: drop=site); merge ");
        if first.site then call execute(cats("ccc(where=(site=",site,") rename=(_x1=site",site,"))"));
        if last then call execute("; by name_order; run;");

        if first.site then name_order=0;
    if first.name then do; _x1=catx(" ",x1); name_order+1; end;
    else if not first.name then _x1=catx(" ",_x1,x1);
    if last.name;
    retain _x1 ;
run;

能否解释一下运行的详细过程,谢谢！

pobel 发表于 2014-10-16 16:22
proc sort data=aaa;
    by name site g;
run;

data ccc;
   set aaa end=last;
   by site name notsorted;

        if _n_=1 then call execute("data ddd(keep=name site: drop=site); merge ");
        if first.site then call execute(cats("ccc(where=(site=",site,") rename=(_x1=site",site,"))"));
        if last then call execute("; by name_order; run;");

        if first.site then name_order=0;
    if first.name then do; _x1=catx(" ",x1); name_order+1; end;
    else if not first.name then _x1=catx(" ",_x1,x1);
    if last.name;
    retain _x1 ;
run;

能否解释一下运行的详细过程,谢谢！

pobel 发表于 2014-10-16 15:23
如果每个site里的name取值的顺序都相同的话：
data ccc;
   set aaa end=last;

1. data aaa;
   
2. infile cards missover;
   
3. input oid site name$ g x1 x2$;
   
4. cards;
   
5. 1 1 c . 10 aaa
   
6. 2 1 a 1 11 ccc
   
7. 3 1 a 2 12 ccc
   
8. 4 1 b . 13 ddd
   
9. 5 2 c . 20   
   
10. 6 2 a . 21   
    
11. 7 2 b 1 22   
    
12. 8 2 b 2 23   
    
13. 9 4 c 1 30   
    
14. 10 4 c 2 31   
    
15. 11 4 a . 32   
    
16. 12 4 b . 33   
    
17. ;
    
18. run;

复制代码

如何再保留c a b对应的oid 和x2的取值？谢谢！
oid  x2
1    aaa
3    ccc
4    ddd