sql合并复杂的表

来源：互联网发布：大庆油田网络客服电话编辑：程序博客网时间：2024/06/05 09:10

这是我在人大经济论坛看到的帖子，张贴如下：

本帖最后由 我想在sas中用sql实现如下问题：
例如有表一：
ID age    sex
1    25    f
2    20    m
表二：
ID product
1    a
1    b
2    a
1    b
2    a
如何合并成这样一个表
ID age  sex    a    b
1    25   f      1    2
2    20   m    2    0

解决：

proc sql;
     create tablec as
     select  A.*
               ,B.product
               ,count(*) ast
     from aA
     join bB
     onA.id=B.id
     group byA.id,age,sex,product ;
quit;

proc transpose data=c out=d(drop=_name_);
     by id agesex;
     var t;
     idproduct;
run;A little simplifaction will save you a transpose :).

proc sql;
  select a.* , a, b
  from a as a
  left join (selectid,  sum(product='a') as a,sum(product='b') as b  from bgroup by id) c
     onA.id=C.id
;
  quit;proc sql;
     create tablec as
     select  A.*
               ,B.product
               ,count(*) ast
     from aA
     join bB
     onA.id=B.id
     group byA.id,age,sex,product ;
quit;

proc transpose data=c out=d(drop=_name_);
     by id agesex;
     var t;
     idproduct;
run;A little simplifaction will save you a transpose :).

proc sql;
  select a.* , a, b
  from a as a
  left join (selectid,  sum(product='a') as a,sum(product='b') as b  from bgroup by id) c
     onA.id=C.id
;
  quit;

0 0