如何寻找离职的人员

本人有一个数据如下：

  FIRM       YEAR         NAME       NAME_ID      

   A            1999         JACK            001         
   A            1999         BEN             002         
   A            1999         GOOD         010         
   A            1999         RICK            011         
   A            1999         XX               012         
   A            1999         XXX             013         
   A            2000         JACK            001         
   A            2000         BEN             002         
   A            2000         Jason          037         
   A            2000         RICK            011        
   A            2000         XX               012         
   A            2000         XXX             013   


   B           1999          ****          ****     
   B           2000          ****         *****


本人想在这个数据中找出每个公司每年人员离职的情况。以上表为例，对比FIRM A 1999和2000年的数据，我们发现ID 为010的员工没有出现在2000年的数据中，这样我们知道010在2000年离职。也就是说判定离职的标准是对于每一个公司，如果一个员工出现在前一年的数据而没有出现在当年的数据中，那么我们判定该员工离职。

检验所有的数据后希望能够调整出一个firm level的统计结果，大概如下图：TURNOVER是一个dummy，如果公司当年有离职，则dummy=1，没有离职则为0。

FIRM   YEAR  TURNOVER
A        1999       0
A        2000       1
A        2001       1
****
B
C
D  

希望各位大神帮忙，谢谢。

带发修行僧 发表于 2014-5-2 23:09
重新试验了一下，仍然还是老问题

我犯了一个错，忘了是在SAS里。去掉了subquery里的order by就一切正常了。

1. proc sql noprint;
   
2.     create table final as
   
3.     select b.firmid
   
4.          , b.fyear
   
5.          , ifn(c.out>0, 1, 0)    as  turnover
   
6.     from (select distinct firmid, fyear from sample1 ) b left join (
   
7.         /*count the number of resigned employee*/
   
8.          select firmid
   
9.               , fyear
   
10.               , count(directorid) as out
    
11.            from (
    
12.             /*exclude retained employee*/
    
13.                 select aa.firmid
    
14.                      , aa.fyear
    
15.                      , aa.directorid
    
16.                      , bb.directorid as tmp
    
17.                 from (select firmid, fyear+1 as fyear, directorid from sample1) aa  /*use the previous year as base*/
    
18.                           left join (
    
19.                         /*retained employee */
    
20.                         select a.firmid
    
21.                              , b.fyear
    
22.                              , a.directorid
    
23.                         from sample1 a, sample1 b
    
24.                         where a.firmid    = b.firmid
    
25.                           and a.directorid = b.directorid
    
26.                           and a.fyear +1 = b.fyear
    
27.                         )bb
    
28.                  on aa.firmid=bb.firmid
    
29.                 and aa.fyear=bb.fyear
    
30.                 and aa.directorid=bb.directorid
    
31.                  )
    
32.            where tmp is missing
    
33.            group by firmid, fyear
    
34.     )c
    
35.     on b.firmid=c.firmid
    
36.    and b.fyear=c.fyear
    
37.     ;

复制代码

1.      %let tbl=YourTableHere;
   
2.     proc sql noprint;
   
3.         create table final as
   
4.         select b.firm
   
5.              , b.year
   
6.              , ifn(c.out>0, 1, 0)    as  turnover
   
7.         from (select distinct firm, year from &tbl order by firm, year) b left join (
   
8.              select firm
   
9.                   , year
   
10.                   , count(name_id) as out
    
11.                from (
    
12.                     select *
    
13.                     from &tbl
    
14.                     except (
    
15.                             select a.*
    
16.                             from &tbl a, &tbl b
    
17.                             where a.firm    = b.firm
    
18.                               and a.name_id = b.name_id
    
19.                               and a.year +1 = b.year
    
20.                             )
    
21.                      )
    
22.                group by firm, year
    
23.         )c
    
24.         ;

复制代码

1. proc sql;
   
2.   create table final as
   
3.     select a.*, b.year as year2,
   
4.       case
   
5.          when year2 ne . then 0
   
6.          else 1
   
7.      end as truncover
   
8.   from  a left join b
   
9.     on a.firm = b.firm and a.name = b.name;
   
10. quit;

复制代码

jeozu 发表于 2014-5-1 10:45

我试验了你的code，SAS显示有如下问题：

437     %let tbl=sample1;
438      proc sql noprint;
439      create table final as
440      select b.firmid
441             , b.fyear
442             , ifn(c.out>0, 1, 0)    as  turnover
443          from (select distinct firmid, fyear from &tbl order by firmid, fyear  )   b left join (
                                                                           -----                         -
                                                                            79                        22
                                                                                                       200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, ',', -,
              '.', /, <, <=, <>, =, >, >=, ?, AND, ASC, ASCENDING, BETWEEN, CONTAINS, DESC,
              DESCENDING, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET,
              NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

444               select firmid
                              ------
                               22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.',
              /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE,
              LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

445                    , fyear
446                    , count(directorid) as out
                                           --
                                           22
                                           202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <,
              <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE,
              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

447                 from (
448                      select *
449                      from &tbl
450                      except (
NOTE: Line generated by the macro variable "TBL".
1     sample1
        -------
         22
        202
451                              select a.*
                                            -
                                           22
                                           76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -,
              '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT,
              IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

452                              from &tbl a, &tbl b
453                              where a.firmid    = b.firmid
454                                and a.directorid = b.directorid
455                                and a.fyear +1 = b.fyear
456                              )
457                       )
458                 group by firmid, fyear
459          )c
460          ;
461          quit;

和你的code相比我只改动了变量名字：table name改为sample1; firm 改为firmid; year 改为fyear; name_id 改为directorid ; 其他没有改动。 能否帮我看看原因？谢谢

苹果叶 发表于 2014-5-1 23:21

我只有一个表格，您的代码中a和b分别代表什么？

带发修行僧 发表于 2014-5-2 20:49
我试验了你的code，SAS显示有如下问题：

437     %let tbl=sample1;

&tbl是宏，把第一行 %let tbl=YourTableHere;　中YourTableHere替换为sample1.

带发修行僧 发表于 2014-5-2 21:16
我只有一个表格，您的代码中a和b分别代表什么？

1. proc sql;
   
2.   create table final as
   
3.     select a.*,
   
4.       case
   
5.          when a.year = XXXX or b.year ne . then 0
   
6.          else 1
   
7.      end as truncover
   
8.   from  a left join a as b
   
9.     on a.firm = b.firm and a.name_id = b.name_id and a.year+1 = b.year
   
10.   where calculated truncover  = 1
    
11.   order by a.firm,a.year;
    
12. quit;

复制代码

看错了，以为是一年一个表格。。 看下这样是否可行，XXXX 代表你数据收集到的最后一年

苹果叶 发表于 2014-5-2 21:47
看错了，以为是一年一个表格。。

是一个面板数据表格，比如说有100个公司10年的数据，每个公司有个人的数据。想根据个人数据判断公司每年是否有人离职

jeozu 发表于 2014-5-2 21:26
&tbl是宏，把第一行 %let tbl=YourTableHere;　中YourTableHere替换为sample1.

这个我已经修改了。刚才贴的log可能位置不对，请看这张图，谢谢