topic Re: how to count entry, exit and unchanged by month in SAS Programming

how to count entry, exit and unchanged by month

Jonate_H — Fri, 03 Mar 2017 16:59:46 GMT

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.

Re: how to count entry, exit and unchanged by month

Reeza — Thu, 02 Mar 2017 07:36:38 GMT

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;

Re: how to count entry, exit and unchanged by month

Reeza — Thu, 02 Mar 2017 07:37:21 GMT

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

Re: how to count entry, exit and unchanged by month

rogerjdeangelis — Thu, 02 Mar 2017 15:04:56 GMT

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

Re: how to count entry, exit and unchanged by month

Jonate_H — Thu, 02 Mar 2017 18:01:12 GMT

Thank you all for the inputs.