topic Re: Nested do loops to compare two sets of variables in SAS Procedures

Nested do loops to compare two sets of variables

mewsmit — Thu, 19 Jan 2017 18:54:13 GMT

Hi!

I have a dataset where each row is a different person and each column is the date that they visited the study site.

I have list of variables: Pdate1-Pdate1360 and Qdate1-Qdate705. I want to create a flag variable called Flagvar that indicates whether any of the Q dates fell within 30 days of any of the Pdates, but I’m new to arrays and not sure where I’m going wrong. Since I already have over 2000 columns I don't want to create a cartesian product. Here is my code:

data want; set have;

array Pdate{1360} Pdate1-Pdate1360;

array Qdate{705} Qdate1-Qdate705;

do k=1 to 1360;

do j=1 to 705;

if intck('day', Pdate{k}, Qdate{j}) le 30

then Flagvar=1;

else Flagvar=0;

end;

run;

Suggestions, please?

Thanks in advance! I'm using SAS 9.4.

Re: Nested do loops to compare two sets of variables

Reeza — Thu, 19 Jan 2017 19:10:15 GMT

Remove the Else condition from your loop. The Else resets the flag as soon as you check the next field.

You may also want to add a LEAVE so you leave the loop when the flag is set to speed this up. Or a while condition to the loop.

Yoir logic and code appears fine otherwise 🙂

Re: Nested do loops to compare two sets of variables

Astounding — Thu, 19 Jan 2017 20:13:10 GMT

Another issue you may need to address ...

Even with ELSE removed, this code gives you the wrong answer. It properly handles PDates that come before QDates, but not the other way around. For those cases, INTCK will give you a negative number, which is always less than 30.

When measuring days, INTCK isn't really needed. You can just subtract one from the other. This might be a better calculation to use:

abs(Pdate{k} - Qdate{k})

Even then, be wary of missing values which will also be less than 30.

Re: Nested do loops to compare two sets of variables

mewsmit — Fri, 20 Jan 2017 13:20:52 GMT

That's a very good point - thanks!