topic Re: 18 Month Date Range with Overlapping Dates in New SAS User

18 Month Date Range with Overlapping Dates

A_Halps — Tue, 22 Jan 2019 14:29:40 GMT

I am trying to look at an 18 month range of dates and label them properly. However, some of the dates overlap.

For example, some of the date ranges will be "7/1/02 - 12/31/03", "7/1/03 - 12/31/04", and "7/1/04 - 12/31/05". I want to a make a new column to show if a specific date is in that date range or not. The only issue I keep having is the overlapping dates. I want to duplicate a row if it is in both date ranges so that new column I created will say each date range. For example, 11/1/03 is in both "7/1/02 - 12/31/03" and "7/1/03 - 12/31/04". So I want the row of data where exam_dt = 11/1/03 to be duplicated so new_exam_date will have one row that says "7/1/02 - 12/31/03" and another row that says "7/1/03 - 12/31/04".

Right now, I have the following code:

data symptoms_new_date;
   set symptoms;
       if exam_dt ge '01jul2001'd and exam_dt le '31dec2002'd
           then new_exam_date="7/1/01 - 12/31/02";
       else if exam_dt ge '01jul2002'd and exam_dt le '31dec2003'd
           then new_exam_date="7/1/02 - 12/31/03";
       else if exam_dt ge '01jul2003'd and exam_dt le '31dec2004'd
           then new_exam_date="7/1/03 - 12/31/04";
       else if exam_dt ge '01jul2004'd and exam_dt le '31dec2005'd
           then new_exam_date="7/1/04 - 12/31/05";
       else new_exam_date=0;
run;

Thank you!

Re: 18 Month Date Range with Overlapping Dates

SuryaKiran — Tue, 22 Jan 2019 14:44:51 GMT

Hello,

Use multiple IF-THEN conditions to create multiple columns based on the date range, later you can use these new columns created to identify if the date falls in more than one group.

data symptoms_new_date;
    set symptoms;
        if exam_dt ge '01jul2001'd and exam_dt le '31dec2002'd
            then date1="7/1/01 - 12/31/02";
        if exam_dt ge '01jul2002'd and exam_dt le '31dec2003'd
            then date2="7/1/02 - 12/31/03";
        if exam_dt ge '01jul2003'd and exam_dt le '31dec2004'd
            then date3="7/1/03 - 12/31/04";
        if exam_dt ge '01jul2004'd and exam_dt le '31dec2005'd
            then date4="7/1/04 - 12/31/05";			
run;

Re: 18 Month Date Range with Overlapping Dates

A_Halps — Tue, 22 Jan 2019 15:04:34 GMT

Thank you, but how would I continue with the following if I used your method:

proc sql;
   create table symptoms_18_mo as
       select b.*
           from arep.pop_2018 a
       left join Symptoms_new_date b
           on a.study_id=b.study_id

           group by a.study_id, b.new_exam_date;

   create table check_symptoms as
       select distinct new_exam_date,
           count(*) as total,
               sum(cough) as cough_abs,
                sum(gerd) as gerd_abs,
        from symptoms_18_mo
           group by new_exam_date;
quit;

proc sort data=symptoms_18_mo;
   by new_exam_date;
run;

proc freq data=symptoms_18_mo;
   tables new_exam_date* (cough gerd) / missing nopercent;
run;

Re: 18 Month Date Range with Overlapping Dates

mkeintz — Tue, 22 Jan 2019 15:18:45 GMT

Your program is almost there, but:

You are using
if ... then ...
else if ... then ...
sequences. This means that a date that could satisfy 2 if tests will only be subject to 1 of the THEN actions, This is because, once a condition is satisfied the ELSE usage prevents consideration of subsequent conditions.
Once you've solved the above, you should issue an OUTPUT statement for each satisfied condition. This will allow you to duplicate rows when a date falls into 2 date ranges:

data symptoms_new_date;
    set symptoms;
        if exam_dt ge '01jul2001'd and exam_dt le '31dec2002'd then do;
          new_exam_date="7/1/01 - 12/31/02";
          output;
        end;
        if exam_dt ge '01jul2002'd and exam_dt le '31dec2003'd then do;
          new_exam_date="7/1/02 - 12/31/03";
          output;
        end;
        if exam_dt ge '01jul2003'd and exam_dt le '31dec2004'd then do;
          new_exam_date="7/1/03 - 12/31/04";
          output;
        end;
        if exam_dt ge '01jul2004'd and exam_dt le '31dec2005'd then do;
          new_exam_date="7/1/04 - 12/31/05";
          output;
        end;
		if exam_dt lt '01jul2001'd or exam_dt>'31dec2005'd then do;
          new_exam_date=0;
          output;
        end;
run;

Re: 18 Month Date Range with Overlapping Dates

SuryaKiran — Tue, 22 Jan 2019 15:23:30 GMT

Create the column you require at the end of the if condition.

new_exam_date=catx(" ",date1,date2,date3,date4);

This will show the two groups if the date exists in both.

Please provide some sample data and your required output for more specific solution.

Re: 18 Month Date Range with Overlapping Dates

A_Halps — Tue, 22 Jan 2019 15:31:07 GMT

This worked! Thank you!!