BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hanahch
Calcite | Level 5

Hello SAS experts,

 

I would like to pull complete BP and BMI data from encounter date closest prior to dx date within 15 days; If none within 15 days then look back up to 30 days. 

 

What I have:

data have;
input id enctype $ (enc_date dx_date)(:mmddyy10.) bp_sys 31-33 bp_dias 35-36 bmi 38-43 datawant 45-46;
format enc_date dx_date mmddyy10.;
datalines;
3531 op 11/27/2016 11/27/2016 120 82
3531 gm 11/27/2016 11/27/2016 120 82 30.22
3590 ip 01/01/2017 01/25/2017 150 58 30.72
3590 gm 01/10/2017 01/25/2017 150 60 30.72
3590 op 01/24/2017 01/25/2017 150 59 30.11
3669 op 05/15/2011 06/08/2011 136 58
3669 op 06/08/2011 06/08/2011 126 60
3669 gm 06/15/2011 06/08/2011 112 58 28.78
3669 op 06/01/2011 06/08/2011
3695 ip 04/27/2014 05/14/2014 125 80
3695 op 05/21/2014 05/14/2014 125 70
3995 op 05/13/2014 05/14/2014 125 80
3995 gm 05/15/2014 05/14/2014 125 70
3744 op 01/02/2018 01/12/2018 114 60 28.96
3744 ip 01/10/2018 01/12/2018
4508 ip 08/12/2018 08/13/2018 162 84 31.6
4524 op 09/22/2017 09/12/2017 110 76 35.79
4704 gm 05/01/2011 05/03/2011 141 74 38.59
4704 op 05/02/2011 05/03/2011 128 70 38.04
7734 op 02/21/2011 02/21/2011
7734 op 07/07/2011 02/21/2011
7734 gm 02/20/2011 02/21/2011 132 64 41.35
8969 ip 03/08/2013 03/26/2013 140 86 31.48
8969 op 03/09/2013 03/26/2013 132 88 31.14
8969 op 02/08/2013 03/26/2013 158 80 32.39
8969 op 04/17/2013 03/26/2013 140 78 31.33
;

data dave; set have;
daydiff=intck('day',dx_date,enc_date);

run;

 

What I want to have:

 

hanahchu_1-1610344345118.png

 

 

Your help is greatly appreciated!  Thank you!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

1) Please supply the test data as a data step not as a picture.

 

2) Try next code:

data temp;
 set have; 
     daydif = dx_date - enc_date;
run;
proc sort data=have; by id enc_date; run;
data want;
set temp; by id; if first.id then flag_out=0; if daydif > 0 and flag_out=0 then do; enc_wnat = 1; flag_out=1; end; run;

3) In case of any issue please explain it.

View solution in original post

4 REPLIES 4
Shmuel
Garnet | Level 18

1) As sas date contains the number of days since 01/01/1960 you can compute

       daydif = enc_date - dx_date;

    and you don't need the intck() function.

 

2) As for assigning value to enc_want, I don't understand what do you mean by

      "complete BP and BMI data from encounter date closest prior to dx date within 15 days;"

    Can you explain by an example?

hanahch
Calcite | Level 5

 

The goal is to pull the most complete vital data on bp_sys, bp_dias and BMI from an encounter that is closest within 15 days prior to dxdate. If an encounter closest to dxdate has the most missing data on all 3 variables then it needs to search the next records until the most complete vital data is found.  If no vital data within 15 days then it needs to search up to 30 days.  Below are some examples of cases that explained how I marked the encounters in the output:

 

ID 3531- the enc_date is not prior to dxdate --> do not mark as 1

ID 3590-closest encounter (1/24/2017) prior to dxdate with complete vital data --> mark as 1

ID 3669-no vital data within 15 days so look back 30 days. Enc_date 5/15/2011 has the most complete vital data  -->  mark as 1

ID  3695-no vital data within 15 days so look back 30 days.  Enc 4/272014 has the most complete vital data --> mark as 1

 

Hope my explanation makes sense.  Thank you for your help!

anming
Pyrite | Level 9

is this what you want?


data dave; set have;
daydiff=intck('day',dx_date,enc_date);
if abs(daydiff) le 15 then enc_want=1;
else if abs(daydiff) gt 15 and abs(daydiff) le 30 then enc_want=1;
run;

or does this one address what you need? 

 

proc sql;
create table close1 as
select *
from dave
where abs(daydiff) le 15;

create table close2 as
select *
from dave
where abs(daydiff) gt 15 and abs(daydiff) le 30;
quit;

 

Shmuel
Garnet | Level 18

1) Please supply the test data as a data step not as a picture.

 

2) Try next code:

data temp;
 set have; 
     daydif = dx_date - enc_date;
run;
proc sort data=have; by id enc_date; run;
data want;
set temp; by id; if first.id then flag_out=0; if daydif > 0 and flag_out=0 then do; enc_wnat = 1; flag_out=1; end; run;

3) In case of any issue please explain it.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

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