DATA Step, Macro, Functions and more

how to count entry, exit and unchanged by month

Reply
Frequent Contributor
Posts: 130

how to count entry, exit and unchanged by month

[ Edited ]

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. 

 

Super User
Posts: 17,936

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

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;

Super User
Posts: 17,936

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

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

Valued Guide
Posts: 505

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

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
Frequent Contributor
Posts: 130

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

Thank you all for the inputs.
Ask a Question
Discussion stats
  • 4 replies
  • 146 views
  • 0 likes
  • 3 in conversation