Блог

Я хочу создать столбец «Вылечено», который принимает значение 1, когда клиент ушел have1 либо на следующий день, либо в течение двух следующих дней после появления have2 . Данные have1 — это набор данных, который определяет, когда клиент вошел в определенный статус и вышел из него. have2 показывает, когда с клиентом связались.

Это код, который я сделал:

 proc SQL;
create table want2 as
select b.*
,     case when b.Outcome = "Answered" or
                b.Outcome = "Answerphone Message Left" or       
                b.Outcome = "Answerphone Message Not Left" or
                b.Outcome = "No Answer"
                and
                a.Start_date <= b.Date <= a.End_date
                and
                a.End_date <= b.Date 2
                then 1 else 0 END as Cured    
                
from have1  a, have2 b
where a.ID=b.ID;
quit;
 

Наборы данных, с которыми я работаю, являются:

 data have1;
infile datalines dlmstr=' | ';
input ID  Start_date :ddmmyy10. End_date :ddmmyy10.;
format date start_date date9.;
datalines;
ID | Start_date | End_date 
1  | 01/01/2021 | 03/01/2021
1  | 20/01/2021 | 21/01/2021
2  | 05/01/2021 | 07/01/2021
3  | 10/01/2021 | 30/01/2021
3  | 25/01/2021 | 25/01/2021
;;;
run;

data have2;
infile datalines dlmstr=' | ';
input ID  Date :ddmmyy10. Outcome ;
format Date date9.;
datalines;
ID | Date       | Outcome
1  | 01/01/2021 | Answered
2  | 05/01/2021 | Asnwerphone Message Left
3  | 12/01/2021 | Answerphone Message Left
3  | 25/01/2021 | No Answer
;;;
run;
 

В этом случае я должен получить следующее:

 ID | Date       | Outcome                   | Cured 
1  | 01/01/2021 | Answered                  | 1  
2  | 05/01/2021 | Asnwerphone Message Left  | 1
3  | 12/01/2021 | Answerphone Message Left  | 0
3  | 25/01/2021 | No Answer                 | 1
 

ID1 вылечен, потому что он ушел have1 через 2 дня после 01/01

ID2 также вылечен, потому что они ушли have1 через 2 дня после 05/01

ID3 не вылечился, потому что они ушли have1 более чем через 15 дней после 12/01

ID3 вылечен, потому что они ушли have1 в тот же день

Это результат с этим кодом:

 ID| Date      | Outcome  | Cured
1   01JAN2021   Answered    1
1   01JAN2021   Answered    0
2   05JAN2021   Asnwerph    0
3   12JAN2021   Answerph    0
3   25JAN2021   No Answe    0
3   12JAN2021   Answerph    0
3   25JAN2021   No Answe    1
 

Что неверно: прежде всего, он дублирует наблюдения, но он не точно выбирает те, которые должны быть 1 в вылеченном (ID 2).

Есть идеи, что может быть не так / отсутствует?

Я подозреваю, что вы хотите:

 select b.*,
       (case when b.Outcome in ('Answered', 'Answerphone Message Left', 
 'Answerphone Message Not Left', 'No Answer') and
                  a.Start_date <= b.Date and
                  b.Date < a.End_date and
                  a.End_date <= b.Date 2
             then 1 else 0 
        end) as Cured 
 

Мне также интересно, действительно ли вам нужно декартово произведение. Обычно вам нужно join определенное join условие.

В любом программном коде (за пределами SAS и SQL) всякий раз, когда вы используете OR with AND , всегда разделяйте условия круглыми скобками:

 CASE 
   WHEN (b.Outcome = "Answered" OR 
         b.Outcome = "Answerphone Message Left" OR 
         b.Outcome = "Answerphone Message Not Left" OR 
         b.Outcome = "No Answer")
        AND
        (a.Start_date <= b.Date < a.End_date)
        AND 
        (a.End_date <= b.Date 2)
   THEN 1 
   ELSE 0
 END AS Cured 
 

А еще лучше, используйте IN operator для сбора значений и избегайте необходимости OR :

 CASE 
   WHEN b.Outcome IN ("Answered",
                      "Answerphone Message Left",
                      "Answerphone Message Not Left",
                      "No Answer")
        AND
        a.Start_date <= b.Date < a.End_date
        AND 
        a.End_date <= b.Date 2
   THEN 1 
   ELSE 0
 END AS Cured 
 

Похоже, вы хотите СГРУППИРОВАТЬ результаты и установить CURE = 1, если КАКАЯ-ЛИБО из конечных дат для этого идентификатора соответствует вашим критериям. Поэтому используйте агрегатную функцию MAX().

Я не уверен, почему вы тестируете значения РЕЗУЛЬТАТА, поскольку в вашем примере нет значений, которые не включены в ваш список значений для проверки.

 data have1;
  input ID  Start_date :yymmdd. End_date :yymmdd.;
  format Start_date End_date yymmdd10.;
datalines4;
1 2021-01-01 2021-01-03
1 2021-01-20 2021-01-21
2 2021-01-05 2021-01-07
3 2021-01-10 2021-01-30
3 2021-01-25 2021-01-25
;;;;

data have2;
  input ID  Date :yymmdd. Outcome $40.;
  format Date yymmdd10.;
datalines4;
1 2021-01-01 Answered
2 2021-01-05 Answerphone Message Left
3 2021-01-12 Answerphone Message Left
3 2021-01-25 No Answer
4 2021-01-25 No Answer
;;;;

proc SQL;
create table want2 as
select b.id
     , b.date
     , b.outcome
     , max(
         case when (a.Start_date <= b.Date <= a.End_date)
               and (a.End_date <= b.Date 2) then 1
         else 0 
       end
       ) as Cured    
from have1 a
 right join have2 b
 on a.id=b.id
group by b.id,b.date,b.outcome
;
quit;
 

Результат:

 Obs    ID          Date    Outcome                     Cured

 1      1    2021-01-01    Answered                      1
 2      2    2021-01-05    Answerphone Message Left      1
 3      3    2021-01-12    Answerphone Message Left      0
 4      3    2021-01-25    No Answer                     1
 5      4    2021-01-25    No Answer                     0
 

PS Отображение дат в порядке D-M-Y или M-D-Y просто запутает половину вашей аудитории.

Сложная логика обычно требует круглых скобок для правильной группировки выражений.

Логический оператор SQL AND имеет приоритет над OR

Линейно закодированный логический оператор, такой как

 P OR Q or R OR S AND X AND Y AND Z
 

вычисляется как

 P or Q or R or (((S and X) and Y) and Z)
 

и, как правило, необходимо исправить, чтобы

 (P OR Q or R OR S) AND X AND Y AND Z
 

Как указано @Parfait, вы можете использовать IN operator, когда P Q R S все выражения имеют дело с сопоставлением одной символьной переменной с несколькими различными терминами. Используйте IN: для сопоставления префиксов терминов.