DATA Step, Macro, Functions and more

create ID based on some conditions

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

create ID based on some conditions

NBRIDDRGSTART_DTEND_DTAdmit_ID
00158409W00100107746-Dec-1129-Jul-121
00158409W00100107746-Dec-1129-Jul-121
00158409W001001077430-Jul-1231-Jul-121
00158409W001001077430-Jul-1231-Jul-121
00211424W021001076516-Jan-1221-Sep-122
00324582W001011977524-Oct-1113-Feb-123
00324582W001011977524-Oct-1113-Feb-123
00330871W01100227665-Jan-1230-Aug-124
00330871W01100227665-Jan-1230-Aug-124
00334334W011001477513-Feb-1224-Oct-125
00334334W011001477513-Feb-1224-Oct-125
00337381W041001977525-Aug-1118-Apr-126
00337381W041001977525-Aug-1118-Apr-126
00340418W011000476620-Mar-1220-Dec-127
00340418W011000476620-Mar-1220-Dec-127
00345727W041001477522-Jul-111-Mar-128
00345727W041001477522-Jul-111-Mar-128
00347643W031001576611-Jan-1227-Apr-129
00347643W031001576611-Jan-1227-Apr-129
00348641W021002077523-Feb-121-Nov-1210
00348641W021002077523-Feb-121-Nov-1210
00355444W001002276610-Jan-1226-Oct-1211
00355444W001002276626-Oct-1226-Nov-1211
00367091W021002277424-Jan-1220-Jul-1212

 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;


Accepted Solutions
Solution
‎10-20-2016 04:52 PM
Super User
Posts: 19,775

Re: create ID based on some conditions

Posted in reply to zhuxiaoyan1

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;

 

View solution in original post


All Replies
Solution
‎10-20-2016 04:52 PM
Super User
Posts: 19,775

Re: create ID based on some conditions

Posted in reply to zhuxiaoyan1

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;

 

Contributor
Posts: 65

Re: create ID based on some conditions

Thank you very much, Reeza! It works perfectly.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 199 views
  • 0 likes
  • 2 in conversation