BookmarkSubscribeRSS Feed
soumri
Quartz | Level 8

Hi everybody,
I have a form of data file like this: (over then 340 000 Obs)

 

 

ID           B_DATE              N_ACT  S_DATE              C_DATE              STS       VAL

BTN01   05/10/2006         1             31/12/2007         02/03/2008         P             15

BTN01   05/10/2006         1             31/12/2007         02/04/2008         M           .

BTN01   05/10/2006         1             31/12/2007         27/04/2008         P             15

BTN01   05/10/2006         1             31/12/2007         02/06/2008         M           .

BTN01   05/10/2006         1             31/12/2007         17/06/2008         P             12

BTN01   05/10/2006         1             31/12/2007         15/07/2008         P             10

BTN01   05/10/2006         1             31/12/2007         23/08/2008         P             9

BTN01   05/10/2006         1             31/12/2007         25/09/2008         T             0

BTN02   26/12/2005         2             09/06/2008         15/07/2008         P             21

BTN02   26/12/2005         2             09/06/2008         23/08/2008         M           .

BTN02   26/12/2005         2             09/06/2008         25/09/2008         M           .

BTN02   26/12/2005         2             09/06/2008         30/10/2008         P             16

BTN02   26/12/2005         2             09/06/2008         11/12/2008         P             14

BTN02   26/12/2005         2             09/06/2008        11/01/2009         P             14

BTN02   26/12/2005         2             09/06/2008        11/02/2009         T             0

BTN07   02/02/2004         2             19/09/2008         25/09/2008         P             17

BTN07   02/02/2004         2             19/09/2008         25/10/2008         P             21

BTN07   02/02/2004         2             19/09/2008         16/12/2008         P             19

BTN07   02/02/2004         2             19/09/2008         25/01/2009         M           .

BTN07   02/02/2004         2             19/09/2008         22/02/2009         M           .

BTN07   02/02/2004         2             19/09/2008         04/04/2009         P             13

BTN07   02/02/2004         2             19/09/2008         13/05/2009         P             12

BTN07   02/02/2004         2             19/09/2008         13/06/2009         M           .

BTN07   02/02/2004         2             19/09/2008         18/07/2009         T             .

.....

ID$= identifiant;

B_Date=Birth date;

ACT= Activity Number;

S_Date= Start date of the activity;

C_DATE=check date;

STS=Status;

VAL=Value;

Some conditions must be filled according to the STS value (it can be P, M, or T):
For the same activity [ACT] (can be 1, 2 or 3) and for the same individual ( over 68 000)
* If STS = "M" (between P) requires that the interval, between C_DATE, of the next observation, with "P" value, and the previous observation, with a "P" value, also, must be <52 (days). If not, all the activity will be eliminated.
* If in case of two successive M, it requires that the interval of C_DATE between, next and previous observations, with "P" values must be <68 (days), if not all of the activity, will be eliminated.
* If there are 3 or more values of M for the same individual and for the same activity, whatever their positions, all the activity will be eliminated.

Please, I need your help, even if it seems easy for most of you.
Thanks a lot.

 

 

 

2 REPLIES 2
Reeza
Super User

Can you please post expected output based on your data?

 

 

ChrisNZ
Tourmaline | Level 20

Like this?

 

data HAVE;
input ID $    B_DATE : ddmmyy. N_ACT :  S_DATE  : ddmmyy.  C_DATE  : ddmmyy.  STS : $1.   VAL : ;
cards;
BTN01   05/10/2006         1             31/12/2007         02/03/2008         P             15
BTN01   05/10/2006         1             31/12/2007         02/04/2008         M           .
BTN01   05/10/2006         1             31/12/2007         27/04/2008         P             15
BTN01   05/10/2006         1             31/12/2007         02/06/2008         M           .
BTN01   05/10/2006         1             31/12/2007         17/06/2008         P             12
BTN01   05/10/2006         1             31/12/2007         15/07/2008         P             10
BTN01   05/10/2006         1             31/12/2007         23/08/2008         P             9
BTN01   05/10/2006         1             31/12/2007         25/09/2008         T             0
BTN02   26/12/2005         2             09/06/2008         15/07/2008         P             21
BTN02   26/12/2005         2             09/06/2008         23/08/2008         M           .
BTN02   26/12/2005         2             09/06/2008         25/09/2008         M           .
BTN02   26/12/2005         2             09/06/2008         30/10/2008         P             16
BTN02   26/12/2005         2             09/06/2008         11/12/2008         P             14
BTN02   26/12/2005         2             09/06/2008        11/01/2009         P             14
BTN02   26/12/2005         2             09/06/2008        11/02/2009         T             0
BTN07   02/02/2004         2             19/09/2008         25/09/2008         P             17
BTN07   02/02/2004         2             19/09/2008         25/10/2008         P             21
BTN07   02/02/2004         2             19/09/2008         16/12/2008         P             19
BTN07   02/02/2004         2             19/09/2008         25/01/2009         M           .
BTN07   02/02/2004         2             19/09/2008         22/02/2009         M           .
BTN07   02/02/2004         2             19/09/2008         04/04/2009         P             13
BTN07   02/02/2004         2             19/09/2008         13/05/2009         P             12
BTN07   02/02/2004         2             19/09/2008         13/06/2009         M           .
BTN07   02/02/2004         2             19/09/2008         18/07/2009         T             .
BTN12   26/12/2005         2             09/06/2008         25/07/2008         P             21
BTN12   26/12/2005         2             09/06/2008         23/08/2008         M           .
BTN12   26/12/2005         2             09/06/2008         25/09/2008         M           .
BTN12   26/12/2005         2             09/06/2008         30/09/2008         P             16
BTN12   26/12/2005         2             09/06/2008         11/12/2008         P             14
BTN12   26/12/2005         2             09/06/2008        11/01/2009         P             14
BTN12   26/12/2005         2             09/06/2008        11/02/2009         T             0
run;
data SET_DELETE_FLAG;
  set HAVE(keep=ID N_ACT C_DATE STS);
  by ID N_ACT C_DATE;
  if first.N_ACT then call missing (NB_M,DELETE_ACT);
  if not DELETE_ACT;
  if STS='M' then NB_M+1;
  if (lag2(ID)=ID & lag2(N_ACT)=N_ACT & lag2(STS)='P' & lag(STS)='M' & STS='P' & dif2(C_DATE) >= 52)
   | (lag3(ID)=ID & lag3(N_ACT)=N_ACT & lag3(STS)='P' & lag2(STS)='M' & lag(STS)='M' & STS='P' & dif3(C_DATE) >= 68)
   | NB_M >=3
   then DELETE_ACT+1; 
   if DELETE_ACT;  %* comment this line to check delete flag in intermediate table SET_DELETE_FLAG; 
   keep ID N_ACT DELETE_ACT ;
run;
data WANT;
  merge HAVE
        SET_DELETE_FLAG ;
  by ID N_ACT ;
  if not DELETE_ACT;
run;

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
  • 2 replies
  • 495 views
  • 0 likes
  • 3 in conversation