topic Re: compare one sets of dates with another sets of dates in SAS Programming

compare one sets of dates with another sets of dates

maxy — Wed, 17 Jun 2020 01:09:04 GMT

My dataset looks like below:

```

ID  Date_of_infusion1  Date_of_infusion2...Date_of_infusion33  Labdate1   Labdate2  ...Labdate100
A     04/01/2016            08/06/2016               .         11/08/2017  10/21/2017      .
B     09/18/2015                .                    .         09/22/2015  09/30/2015      .
C     11/24/2015                .                    .         07/05/2015      .           .

```

What I want to do: For each ID, if the labdate is after any of the infusion dates but within 4 months of that date(any infusiondate<=labdate<=the infusiondate+4months), then keep it; otherwise drop the labdate.

What I was trying to do: make 2 arrays,one for infusion dates one for labdates. Then create a "flag" variable associated with Labdate.

I wrote something like this:

array infusion {*} date_of_infusion:;
array labdate {*} labdate:;
array flag {101} flag0-flag100;

do i= 1 to dim(labdate);
    do j=1 to dim(infusion);
    if infusion[j] ne . and infusion[j]<=labdate[i]<=intnx('month',infusion[j],4) then flag[i]=1;else flag[i]=0;
    end;
end;

However, the result doesn't look like something I want...

Any help is appreciated! Thanks!

Re: compare one sets of dates with another sets of dates

r_behata — Wed, 17 Jun 2020 01:42:08 GMT

Assuming Each Flag variable corresponds to a Labdate.

data want;
	set have;
	array infusion {*} date_of_infusion:;
	array labdate {*} labdate:;
	array flag {101} flag0-flag100;


		do i= 1 to dim(labdate);
			do j=1 to dim(infusion);
				if infusion[j] ne . and infusion[j]< labdate[i]<=intnx('month',infusion[j],4) then
					do;
						flag[i]=1;
						leave;
					end;
				else flag[i]=0;
			end;
		end;
run;

Re: compare one sets of dates with another sets of dates

ChrisNZ — Wed, 17 Jun 2020 02:14:28 GMT

> otherwise drop the labdate

What does this mean? You can't drop a column.

Re: compare one sets of dates with another sets of dates

andreas_lds — Wed, 17 Jun 2020 04:53:19 GMT

Please:

post data in usable form
post the expected result with the data you have posted
reduce the data, so that is only shows all cases you have/expect

Have you thought about normalizing the data? Having numbered columns is almost always a sign for bad data-design. In your case having two tables, one with ID and one infusion_date and the other table with ID and one labdate, maybe a sql-merge could solve the problem.