topic Re: Replacing Invalid Records Using Data from Another Record in SAS Programming

Replacing Invalid Records Using Data from Another Record

Sumin1 — Thu, 05 Dec 2024 22:00:48 GMT

I am currently working on a dataset which contains records of daily time use for a large number of participants (American Time Use Survey activity data). I am trying to modify some data before proceeding with the next steps, but my SAS code is not working as intended.

Here’s the condition I am trying to implement in the code:

If a participant (same TUCASEID) has only two commute periods (TRCODE in 180501, 180503, 180504), where:
- One period occurs right before work (TUTIER2CODE=05) and the other right after work.
- he two periods differ by less than 15 minutes in duration (TUACTDUR24).
- One record of commute modality is valid (TEWHERE between 12 and 21), but the other is invalid (TEWHERE less than 12).

In this case, I want to replace the invalid modality with the valid modality.

I tried the code below, but the results are different from what I expected. Which parts need to be changed?

data replaced_2023; set baseline_2023;

by TUCASEID;

if TRCODE in (180501, 180503, 180504) then do;

if 12 <= TEWHERE <= 21 then valid_modality = 1;

else if TEWHERE < 12 then valid_modality = 0;

end;

retain prev_TUTIER1CODE prev_TUACTDUR24 prev_TEWHERE prev_valid_modality;

if first.TUCASEID then do;

prev_TUTIER1CODE = .;

prev_TUACTDUR24 = .;

prev_TEWHERE = .;

prev_valid_modality = .;

end;

if TUTIER1CODE = 05 and prev_TUTIER1CODE = 05 and

abs(TUACTDUR24 - prev_TUACTDUR24) < 15 and

prev_valid_modality = 1 and valid_modality = 0 then TEWHERE = lag(prev_TEWHERE);

run;

data last_replaced_2023; set replaced_2023;

by TUCASEID;

if TRCODE in (180501, 180503, 180504) then do;

if 12 <= TEWHERE <= 21 then valid_modality = 1;

else if TEWHERE < 12 then valid_modality = 0;

end;

retain next_TUTIER2 next_TUACTDUR24 next_TEWHERE next_valid_modality;

if first.TUCASEID then do;

next_TUTIER1CODE = .;

next_TUACTDUR24 = .;

next_TEWHERE = .;

next_valid_modality = .;

end;

if TUTIER1CODE = 05 and prev_TUTIER1 = 05 and

abs(TUACTDUR24 - prev_TUACTDUR24) < 15 and

next_valid_modality = 1 and next_modality = 0 then TEWHERE = lag(next_TEWHERE);

run;

Re: Replacing Invalid Records Using Data from Another Record

ballardw — Thu, 05 Dec 2024 22:26:37 GMT

Example input data in the form of a working data step for the variables needed. That way we can test code.

Then provide for the example what the expected/desired result is.

Did your log show any interesting messages?

Are all of those code variables actually numeric?

You have a comment about "15 minutes". Are the variables involved SAS TIME or DATEtime variables? Comparisons as written would be looking at 15 second differences. If the values are not SAS time or datetime values then there can be all sorts of issues with how the actual values are stored and what is needed to determine "15 minutes". It may help to provide the output from Proc Contents for your data set Baseline_2023 to clarify any questions about your variables.

Re: Replacing Invalid Records Using Data from Another Record

quickbluefish — Fri, 06 Dec 2024 01:01:45 GMT

Definitely recommend posting a working input dataset as @ballardw suggested. The one thing that jumps out is that you're using LAG() conditionally, which is rarely going to do what you expect. Most of the time, if you're using LAG(), results are much more predictable if you create a variable *un*conditionally (that is, not part of an IF statement / block), then instead use that variable where you're currently using LAG in your code:


lag_val = lag(val);
if x>y then newval=lag_val;

Re: Replacing Invalid Records Using Data from Another Record

mkeintz — Fri, 06 Dec 2024 02:38:26 GMT

Please show some sample data, in the form of a working DATA step. Then show us what you want the result of that sample to look like.

Help us help you.