Calcite | Level 5

## Finding Missing Values

Hello, I need to find if certain dates are missing in the sample data below:

An animal should have an entry_date for every day in between trt_1 and trt_2 and trt_2 and trt_3.  There should be 14 entry_dates in between trt_1 and trt_2, trt_2 and trt_3, and then 14 days after trt_3.  I'm not sure how to find if I'm missing any of these dates?

Animal   entry_date   trt_1            trt_2            trt_3

1           18JUN21     17JUN21     28JUN21    13SEP21

1           19JUN21     17JUN21     28JUN21    13SEP21

1           20JUN21     17JUN21     28JUN21    13SEP21

1           21JUN21     17JUN21     28JUN21    13SEP21

1           22JUN21     17JUN21     28JUN21    13SEP21

1           24JUN21     17JUN21     28JUN21    13SEP21

For example, the data above is not a full data set, but it should be flagged that 23JUN21 is missing for animal 1

3 REPLIES 3
Diamond | Level 26

## Re: Finding Missing Values

@jmmedina252 wrote:

Hello, I need to find if certain dates are missing in the sample data below:

An animal should have an entry_date for every day in between trt_1 and trt_2 and trt_2 and trt_3.  There should be 14 entry_dates in between trt_1 and trt_2, trt_2 and trt_3, and then 14 days after trt_3.  I'm not sure how to find if I'm missing any of these dates?

Animal   entry_date   trt_1            trt_2            trt_3

1           18JUN21     17JUN21     28JUN21    13SEP21

Please elaborate on this. trt_1 and trt_2 are not 14 days apart, how can there be 14 entry dates? Similarly trt_2 and trt_3 are not 14 days apart. Should be be looking for any missings between trt_1 and (14 days after trt_3)?? What is the role of trt_2 here?

--
Paige Miller
Calcite | Level 5

## Re: Finding Missing Values

yes, you are right this is my mistake. Basically there should be trt1_st and trt1_end to signify the start and end dates of each treatment and then between end of one treatment and start of another should be 14 days.
Diamond | Level 26

## Re: Finding Missing Values

@jmmedina252 wrote:
yes, you are right this is my mistake. Basically there should be trt1_st and trt1_end to signify the start and end dates of each treatment and then between end of one treatment and start of another should be 14 days.

Still not clear. Especially this part: between end of one treatment and start of another should be 14 days.

Please show us a more complete output, where the 14 days is clearly explained and visible in the output

--
Paige Miller
Discussion stats
• 3 replies
• 152 views
• 0 likes
• 2 in conversation