BookmarkSubscribeRSS Feed
radhikaa4
Calcite | Level 5

Hi. I am trying to replace missing date values to expected date values if it falls before the max dates for EACH subject ID.

 

Here is the dataset

subject_idvisit_dayexpected_visit_dateactual_visit_date
111/1/20171/1/2017
121/2/20171/2/2017
131/3/2017 
141/4/20171/4/2017
212/1/20172/1/2017
222/2/2017 
232/3/2017 
242/4/20172/4/2017
252/5/2017 
262/6/20172/6/2017
313/1/20173/1/2017
323/2/20173/2/2017
333/3/20173/3/2017
343/4/20173/4/2017
353/5/20173/5/2017
363/6/2017 

 

Here is the dataset that I would like:

 

subject_idvisit_dayexpected_visit_dateactual_visit_datevisit_date_new
111/1/20171/1/20171/1/2017
121/2/20171/2/20171/2/2017
131/3/2017 1/3/2017
141/4/20171/4/20171/4/2017
212/1/20172/1/20172/1/2017
222/2/2017 2/2/2017
232/3/2017 2/3/2017
242/4/20172/4/20172/4/2017
252/5/2017 2/5/2017
262/6/20172/6/20172/6/2017
313/1/20173/1/20173/1/2017
323/2/20173/2/20173/2/2017
333/3/20173/3/20173/3/2017
343/4/20173/4/20173/4/2017
353/5/20173/5/20173/5/2017
363/6/2017  

 

If the Subject ID 3 does not have any max date value so it is left black since the VISIT was not completed.

 

I have tried the following code

proc sql;

 select coalesce(actual_visit_date,expected_visit_date) as new_visit_date

  from have;

quit;

 

Thanks!

Radhika

8 REPLIES 8
novinosrin
Tourmaline | Level 20

what if

subject 3 happens to be

 

3 1 3/1/2017 3/1/2017
3 2 3/2/2017 3/2/2017
3 3 3/3/2017  
3 4 3/4/2017  
3 5 3/5/2017 3/5/2017
3 6 3/6/2017  

 

would you fill 3,4 ? or leave it blank>?

radhikaa4
Calcite | Level 5

Great question! If the actual visit dates are blank, but observations before and after are filled then they do get replaced.

 

313/1/20173/1/2017
323/2/20173/2/2017
333/3/2017 3/3/2017
343/4/2017 3/4/2017
353/5/20173/5/2017
363/6/2017 
novinosrin
Tourmaline | Level 20

Thank you @radhikaa4

 

data have;
infile cards truncover;
input subject_id	visit_day	expected_visit_date :mmddyy10.	actual_visit_date :mmddyy10.;
format expected_visit_date mmddyy10.	actual_visit_date mmddyy10.;
cards;
1	1	1/1/2017	1/1/2017
1	2	1/2/2017	1/2/2017
1	3	1/3/2017	 
1	4	1/4/2017	1/4/2017
2	1	2/1/2017	2/1/2017
2	2	2/2/2017	 
2	3	2/3/2017	 
2	4	2/4/2017	2/4/2017
2	5	2/5/2017	 
2	6	2/6/2017	2/6/2017
3	1	3/1/2017	3/1/2017
3	2	3/2/2017	3/2/2017
3	3	3/3/2017	3/3/2017
3	4	3/4/2017	3/4/2017
3	5	3/5/2017	3/5/2017
3	6	3/6/2017	 
;



data want;
call missing(f);
do until(last.subject_id);
set have;
by subject_id;
if not missing(actual_visit_date) then f=actual_visit_date;
end;
do until(last.subject_id);
set have;
by subject_id;
actual_visit_date=ifn(expected_visit_date<= f and missing(actual_visit_date),expected_visit_date,actual_visit_date);
output;
end;
drop f;
run;
novinosrin
Tourmaline | Level 20

Oops you wanted a new column visit_date_new

 

data want;
call missing(f);
do until(last.subject_id);
set have;
by subject_id;
if not missing(actual_visit_date) then f=actual_visit_date;
end;
do until(last.subject_id);
set have;
by subject_id;
visit_date_new=ifn(expected_visit_date<= f and missing(actual_visit_date),expected_visit_date,actual_visit_date);
output;
end;
drop f;
format visit_date_new mmddyy10.;
run;
novinosrin
Tourmaline | Level 20

And proc sql-->

 

proc sql;
create table want as
select *,ifn(expected_visit_date<= max(actual_visit_date),expected_visit_date,actual_visit_date) as new_visit_Date format=mmddyy10.
from have
group by subject_id
order by subject_id, visit_day;
quit;
singhsahab
Lapis Lazuli | Level 10
data have;
input subject_id visit_day expected_visit_date : mmddyy10. actual_visit_date mmddyy10.;
format expected_visit_date actual_visit_date mmddyy10.;
datalines;
1 1 1/1/2017 1/1/2017
1 2 1/2/2017 1/2/2017
1 3 1/3/2017 
1 4 1/4/2017 1/4/2017
2 1 2/1/2017 2/1/2017
2 2 2/2/2017
2 3 2/3/2017
2 4 2/4/2017 2/4/2017
2 5 2/5/2017
2 6 2/6/2017 2/6/2017
3 1 3/1/2017 3/1/2017
3 2 3/2/2017 3/2/2017
3 3 3/3/2017 3/3/2017
3 4 3/4/2017 3/4/2017
3 5 3/5/2017 3/5/2017
3 6 3/6/2017 
;
run;


proc sql;
create table want as
select Subject_id,visit_day,expected_visit_date,coalesce(actual_visit_date,newd) as actual_visit_date format=mmddyy10. from
(select Subject_id,visit_day,expected_visit_date,actual_visit_date,case when max(actual_visit_date) gt 0 and (v gt visit_day) then coalesce(actual_visit_date,expected_visit_date) else . end as newd  format=mmddyy10. from
(select Subject_id, visit_day , expected_visit_date,actual_visit_date,max(visit_day) as v from have group by Subject_id)) order by subject_id,visit_day;
quit;
Allaluiah
Quartz | Level 8

Hi @singhsahab Appreciate your effort and attempt. However, the code doesn't look very desirable from the point of view ease of use or even application of logic. From the start of typing to , testing and moving to production and then post production maintenance, I guess it's hard to envision implementing your code. I wonder how your code can be an alternative to the rest. Nevertheless, good contribution 

singhsahab
Lapis Lazuli | Level 10
Hi Allaluiah,

I tried to solve the problem in other way. Thank you for your comment. Here is the simple code ..




proc sql;
create table want as
select subject_id ,visit_day ,expected_visit_date,actual_visit_date , case when expected_visit_date <= max(actual_visit_date) then
coalesce(actual_visit_date,expected_visit_date) else . end as new_date format=mmddyy. from have group by subject_id order by subject_id,visit_day;
quit;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2782 views
  • 0 likes
  • 4 in conversation