create ID based on some conditions

[zhuxiaoyan1]

NBR	ID	DRG	START_DT	END_DT	Admit_ID
00158409W00	10010	774	6-Dec-11	29-Jul-12	1
00158409W00	10010	774	6-Dec-11	29-Jul-12	1
00158409W00	10010	774	30-Jul-12	31-Jul-12	1
00158409W00	10010	774	30-Jul-12	31-Jul-12	1
00211424W02	10010	765	16-Jan-12	21-Sep-12	2
00324582W00	10119	775	24-Oct-11	13-Feb-12	3
00324582W00	10119	775	24-Oct-11	13-Feb-12	3
00330871W01	10022	766	5-Jan-12	30-Aug-12	4
00330871W01	10022	766	5-Jan-12	30-Aug-12	4
00334334W01	10014	775	13-Feb-12	24-Oct-12	5
00334334W01	10014	775	13-Feb-12	24-Oct-12	5
00337381W04	10019	775	25-Aug-11	18-Apr-12	6
00337381W04	10019	775	25-Aug-11	18-Apr-12	6
00340418W01	10004	766	20-Mar-12	20-Dec-12	7
00340418W01	10004	766	20-Mar-12	20-Dec-12	7
00345727W04	10014	775	22-Jul-11	1-Mar-12	8
00345727W04	10014	775	22-Jul-11	1-Mar-12	8
00347643W03	10015	766	11-Jan-12	27-Apr-12	9
00347643W03	10015	766	11-Jan-12	27-Apr-12	9
00348641W02	10020	775	23-Feb-12	1-Nov-12	10
00348641W02	10020	775	23-Feb-12	1-Nov-12	10
00355444W00	10022	766	10-Jan-12	26-Oct-12	11
00355444W00	10022	766	26-Oct-12	26-Nov-12	11
00367091W02	10022	774	24-Jan-12	20-Jul-12	12

 The above is my data. The Admit_ID is the variable that I need to create. my logic is not working. here is my code:

data want1;

set IPDat;

lag_NBR=lag(MBR_NBR);

lag_Prov=lag(PROV_ID);

lag_DRG=lag(DRG);

lag_Start=lag(EPI_START_DT);

lag_END=lag(EPI_END_DT);

if (MBR_NBR ne lag_NBR or Prov_ID ne lag_prov or DRG ne lag_DRG or EPI_START_DT ne lag_Start or EPI_END_DT ne lag_END) and

(EPI_START_DT - lag_END) not in (0, 1)

then counter + 1;

format lag_start lag_End date9.;

run;

[Reeza · Accepted solution]

You're on the correct path.

However, you should make use of the BY variables as well.

Calculating the difference between start date  and previous end date is the biggest issue otherwise.

Untested, but should be close to what you need.

Also, this is asked every week so if you search on here you'll find many examples of how to solve this question.

data want;
set have;
by nbr id;

retain admit_id;

prev_end =lag(end_dt);

if not first.id and (start_dt - prev_end) > 1 then admit+1;
 
else if first.id then admit_id+1;

/*else you do nothing to keep the same value*/

run;

[zhuxiaoyan1]

Thank you very much, Reeza! It works perfectly.