Finding values of one variable in a time period based on another variable

[Pip Clery]

Hello,

I have data from an app where each observation is a user's entry into the app on any given day. Users have IDs. There are multiple rows per ID.

Users answer a question in the app, called "asrm", every 14 days which yields a score where values >0. There is some missingness for this and users don't always answer questions every 14 days.
I have a date variable "asrm_date" for when there is a value for "asrm".

Users have values for another variable "temp" both on the asrm dates but also on other dates.
This can be both positive and negative numbers. Missing is coded as "."

I am interested in whether there is any value for "temp" in the 14 days prior to the asrm date.

Ultimately, I am looking for code to create a binary variable (yes/no):
for each value (which is a date in the variable asrm_date), is there anyvalue for the variable temp in the 14 days before the aforementioned date value

I have tried using Lag functions and code such as:


// Loop through each observation foreach i in 1/`=_N' {
local current_date = asrm_date[`i']
local window_start = `i' - 14
if `window_start' < 1 {
local window_start = 1 }

// Calculate the date range for the 14 days before the current date local start_date = asrm_date[`window_start']

// Check if any value of temp_avg exists within the date range
local has_temp_avg = 0
forval j = `window_start' to `i' {
if asrm_date[`j'] >= `start_date' & asrm_date[`j'] <= `current_date' {
if temp_avg[`j'] != . {
local has_temp_avg = 1
exit // Exit the loop once a value is found within the range
} } }

// Assign the result to the new variable replace temp_avg_14days = `has_temp_avg' in `i' }


But this yields "invalid syntax" error.

Any ideas on how I can do this?

[Andrew Musau]

Welcome to Statalist. Please show us a data example using the dataex command with observations relating to 2-3 IDs (refer to FAQ Advice #12 for details).