BookmarkSubscribeRSS Feed
Jonate_H
Quartz | Level 8

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. 

 

4 REPLIES 4
Reeza
Super User

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
Super User

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

rogerjdeangelis
Barite | Level 11

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
Quartz | Level 8
Thank you all for the inputs.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 734 views
  • 0 likes
  • 3 in conversation