how to count entry, exit and unchanged by month

[Jonate_H]

I tried to create three variables: exit_count, entry_count, and unchanged_count for each month based on id.

1) year1993 month9 is the starting month, so exit_count, entry_count, and unchanged_count are all set to 0.

2) next, compare year1993 month10 with month9, we find that aa, bb, cc are "unchanged", while dd and ee drop out, so they are "exit", and ff does not show up in prior month, so it is "entry".

3) move to year1993 month11, compared with year1993 month10, only bb, cc are "unchanged", and aa, ff drop out, while dd, ee,gg, and ll are new entry.

so, I compare month t with month t-1, to count how many "old" id drop out or remain unchanged, and how many new id show up. 

Originally, I was thinking to use the rolling 2-month window (e.g., put 1993.9 and 1993.10 together), then count the id, if appear twice put into unchanged group; but if appear only once, it is difficult to tell whether it should be in exit or entry group. 

[Reeza]

Sort by Id and date. 

Use BY groups to identify the first and last records and anything else as an unchanged. 

For records where it's a single record,  (first.id = last.id) then I would count it as both entry and exit. 

Proc sort data =have; by Id year month;;

data want;

set have; by Id;

Entry=0; Exit=0; unchanged =0;

if first.id then entry=1;

if last.id then exit+1;

if first.id =0 and last.id=0 then unchanged+1;

run;

[Reeza]

Obviously untested since I'm not typing out your data. 

[rogerjdeangelis]

Building on Reeza understanding of the problem.

I added unchanged counts as a post script?

SOAPBOX ON
No need for proc import.
Wish SAS would deprecate 'proc import' and build more
libname engines and not do the opposite.

I do not mind downloading xls files. I have
very strong security.

Benefit outweighs the downside.
SOAPBOX OFF


HAVE The excel sheet below
==========================

Up to 40 obs from d:/xls/exit_entry_unchange_count.xlsx  total obs=35

        A         B                 C        D         E           F

Up to 40 obs from xel.'sheet1$'n total obs=35

                                         TOTAL_   EXIT_   ENTRY_   UNCHANGED_
     Obs   YEAR   QUARTER   MONTH   ID    COUNT   COUNT    COUNT      COUNT

       1   1993      3         9    aa      5       0        0          0
       2   1993      3         9    bb      5       0        0          0
       3   1993      3         9    cc      5       0        0          0
       4   1993      3         9    dd      5       0        0          0
       5   1993      3         9    ee      5       0        0          0
       6   1993      4        10    aa      4       2        1          3
       7   1993      4        10    bb      4       2        1          3
       8   1993      4        10    cc      4       2        1          3
       9   1993      4        10    ff      4       2        1          3
      10   1993      4        11    bb      6       2        4          2


WANT
====
                                               TOTAL_ EXIT_ ENTRY_ UNCHANGED
Obs ID ENTRY EXIT UNCHANGED YEAR QUARTER MONTH  COUNT COUNT  COUNT    COUNT

  1 aa   1     0      0     1993    3       9     5     0      0        0
  2 aa   0     0      1     1993    4      10     4     2      1        3
  3 aa   0     0      1     1994    1       2     3     2      1        2
  4 aa   0     1      0     1994    1       3     5     0      2        3
  5 bb   1     0      0     1993    3       9     5     0      0        0
  6 bb   0     0      1     1993    4      10     4     2      1        3
  7 bb   0     0      1     1993    4      11     6     2      4        2
  8 bb   0     1      0     1993    4      12     5     1      0        5
  9 cc   1     0      0     1993    3       9     5     0      0        0
 10 cc   0     0      1     1993    4      10     4     2      1        3


DETAILS
=======

    1. Sort by ID

         Obs ID ENTRY UNCHANGED EXIT

           1 aa   1       0       0  Entry = 1
           2 aa   0       1       0  Unchanged  1
           3 aa   0       1       0  Unchanged still 1
           4 aa   0       0       1  Exit set to 1

           5 bb   1       0       0
           6 bb   0       1       0
           7 bb   0       1       0
           8 bb   0       0       1

WORKING CODE (very slight change to Reeza's assumes you do not want to count
unchanged.
============================================================================

  if first.id                  then entry    =1;
  if last.id                   then exit     =1;
  if first.id =0 and last.id=0 then unchanged=1;


FULL SOLUTION
=============

libname xel "d:/xls/exit_entry_unchange_count.xlsx";
Proc sort data =xel.'sheet1$'n out=have;
by Id year month;
run;quit;

data want;
  retain id '  ' entry unchanged exit 0;

  set have;
  by id;

  entry     =0;
  exit      =0;
  unchanged =0;

  if first.id                  then entry    =1;
  if last.id                   then exit     =1;
  if first.id =0 and last.id=0 then unchanged=1;

  output;

run;quit;

PS If you want to count the unchanged

data wantcnt(drop=unchange_cnt);

  do until (last.id);
     set want;
     by id;
     unchange_cnt= sum(unchange_cnt,unchanged);
  end;
  do until (last.id);
     set want;
     by id;
     if unchanged=1 then unchanged=unchange_cnt;
     output;
  end;

  unchange_cnt=0;

run;quit;


Up to 40 obs WORK.WANTCNT to


Obs ID ENTRY UNCHANGED EXIT

  1 aa   1       0       0
  2 aa   0       2       0
  3 aa   0       2       0
  4 aa   0       0       1
  5 bb   1       0       0
  6 bb   0       2       0
  7 bb   0       2       0
  8 bb   0       0       1

[Jonate_H]

Thank you all for the inputs.