Assuming the input lay out is what you have and the data is ordered by id things are little simpler. You have the following scenarios 1. What if the by group starts with a missing value and the first non miss value in the by group is 1? 2. The first value is missing but all the other values are okay   Assumption: Data is already ordered by id and everything is in place       data example; input ID $ seq_have data; cards; 1 1 111 1 2 222 1 . 333 2 . 444 2 2 222 2 3 333 3 . 123 3 . 123 3 . 123 4 4 444 4 . 555 ; run; data example2; set example; by id; retain have_seq; if first.id then do; if missing(seq_have) then do; have_seq=1; end; else do; have_seq=seq_have; end; end; else do; if ^missing(seq_have) then have_seq=seq_have; if missing(seq_have) then do; have_seq=have_seq+1; *have_seq = seq_have; end; end; run;       ... View more

Hi Samantha, Thanks for your reply but the suggested approach does work for direct match (using index function) but fails to capture variations since variation is within a large string , so can't do a direct spedis or soundex activity for it. Nonetheless thanks again and any further advise is highy valued. Regards ... View more

data new; informat final_stop_date_time start_date_time_1 stop_date_time_1 datetime19.; input SubjectID final_stop_date_time start_date_time_1 stop_date_time_1 group $; format final_stop_date_time start_date_time_1 stop_date_time_1 datetime19.; if group = '2' and (stop_date_time_1 <= final_stop_date_time) then keep = 1 ; if (group = '2' and ((start_date_time_1 >= final_stop_date_time ) and missing(stop_date_time_1))) OR (group = '2' and (stop_date_time_1 > final_stop_date_time)) then keep = 0; cards;;; 11 12FEB2020:10:59:00 11FEB2020:15:30:00 11FEB2020:16:08:00 2 11 12FEB2020:10:59:00 11FEB2020:16:08:00 12FEB2020:10:59:00 2 11 12FEB2020:10:59:00 12FEB2020:21:40:00 . 2 ;;; run; reasons why it did not work before: 1. (stop_date_time_2 > final_stop_date_time)  stop_date_time_2 does not exist 2. It did not go into else condition as when the date column is missing first if condition is always true ... View more

IMHO, don't use SQL for purposes it wasn't designed for. The code you've supplied isn't easily translatable into T-SQL. What is wrong with continuing to process in SAS and then load the results into the database? ... View more

Show the log for your data step. Show the code and any messages. Copy from the LOG and paste into a code box opened on the forum with the</> icon. The code box is important because the message windows will reformat text including SAS generated diagnostic text.   (group = '2' and ((start_date_time_1 > final_stop_date_time ) and missing(stop_date_time_1)) OR (group = '2' and (stop_date_time_2 > final_stop_date_time)) count the ( and ) you have mismatched parentheses. ... View more

Arrays are the correct approach here, though another option would be to transpose your data and operate on a long format. I suspect in general, the long format is better.    Here's a tutorial on using Arrays in SAS https://stats.idre.ucla.edu/sas/seminars/sas-arrays/   Here is a reference that illustrates how to refer to variables and datasets in a short cut list: https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html   Transposing data tutorials: Wide to Long: https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/ https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/ ... View more

Please report your results. What have you tried and what results did you get? ... View more