topic Re: How do I check chronological order of event datetimes? in SAS Programming

How do I check chronological order of event datetimes?

epibio2021 — Wed, 06 Jan 2021 20:27:06 GMT

Hi SAS users,

My dataset has 35 variables that are in SAS datetime format, for ex:

16APR2019:21:22:00

I am trying to check the chronological order of these variables, where var1 occurred before var2, var 2 before var3, ...var34 before var35 for all 35 variables. For each variable, there is either a datetime value or a missing value. This needs to be done for each case ID.

So for case #1, I need to check that var1 occurred before var2, var 2 before var3,...var 35. Repeat for all cases. My goal is to flag observations if the events are not in chronological order. I am a beginner and having difficulty approaching how to do this.

Thank you, any help would be appreciated.

Re: How do I check chronological order of event datetimes?

Jagadishkatam — Wed, 06 Jan 2021 20:28:48 GMT

could you please post sample data.

Re: How do I check chronological order of event datetimes?

epibio2021 — Wed, 06 Jan 2021 20:36:59 GMT

Sample Data:

Obs	var1	var2	var3
1	04MAY2018:13:30:00	04MAY2018:18:35:00	06MAY2018:14:07:00
2	04MAY2018:17:00:00	05MAY2018:00:43:00	.
3	09MAY2018:06:55:00	09MAY2018:12:00:00	14MAY2018:11:23:00
4	12MAY2018:20:00:00	.	14MAY2018:17:30:00
5	14MAY2018:14:00:00	14MAY2018:17:50:00	15MAY2018:11:37:00

For each obs, I need to check that var 1 occured before var 2, and var 2 occured before var 3.

Re: How do I check chronological order of event datetimes?

Tom — Wed, 06 Jan 2021 20:43:22 GMT

What is the answer you want for that example input?

Re: How do I check chronological order of event datetimes?

ballardw — Wed, 06 Jan 2021 20:50:53 GMT

What does the result look like?

Do you want one variable that indicates "all in order"/"not in order" or do you want 34 variables showing the comparisons of var1 to var2, var2 to var3, var3 to var4, ..., var34 to var35?

And HOW are missing values actually treated? Ignored in comparisons? Always "out of order"? Out of order if other than the first (or last or some other rule)?

SAS will allow you to compare multiple variables at one time like: v1 < v2 < v3 <v4< ...<v35.

But if you use this the missing value is always less than anything. So any missing other than in the left most variables the result for the total comparison will be "false". So how missing is to be treated needs to be stated explicitly. If you want pairwise comparison results then the rule for how missing needs to be provided as well as you have 3 cases to consider involving missing:

Var1 missing , Var2 not missing

Var1 not missing, Var2 missing

Var1 missing , Var2 missing

So rules involving all of these cases would be needed.

Re: How do I check chronological order of event datetimes?

epibio2021 — Wed, 06 Jan 2021 21:01:10 GMT

I would like one variable to indicate that chronological order is "good" or "not good".

I currently have the following:

if var1 <= var2 <= var3 .... <= var35 then CHRON = 'Good';

As you've stated, if any of the 35 variables is missing data, CHRON returns a missing value. I would like any missing data to be ignored and skip to the next variable.

So if var2 and var5 are missing data, I would like it to check that var1<var3<var4<var6 = good/not good.

Thank you.

Re: How do I check chronological order of event datetimes?

mkeintz — Wed, 06 Jan 2021 21:09:45 GMT

The program below compares neighboring variables. If the, say, 6th variable is less than the 5th, then flag=6. If all 35 variables are in non-decreasing order, then flag=0.

data want;
   set have;
   array dtimes {*}   var1-var35;
   do flag=2 to dim(dtimes) until(dtimes{flag}<dtimes{flag-1});
   end;
   if flag=dim(dtimes)+1 then flag=0;
end;

Note that missing date values compare as less than all non-missing data values.

Re: How do I check chronological order of event datetimes?

dikang77 — Tue, 08 Mar 2022 21:51:22 GMT

Works for me! Thanks!