topic Re: Compare dates within the same record in SAS Procedures

Compare dates within the same record

ChrisTurtle — Wed, 01 Apr 2020 15:29:05 GMT

After some guidance as to how best to get started. I will have a dataset similar to below where I will have approximately 200 date fields and 100 IDs:

ID	date_id	another_date_id	yet_another_date_id
1	01/01/2019	02/01/2019	05/01/2019
2	02/01/2019	05/01/2019	06/01/2019

I am wanting to know if there is a simpler way to do logical calculations.

e.g. if yet_another_date_id > all other date_ids without typing them all out?

Re: Compare dates within the same record

Reeza — Wed, 01 Apr 2020 15:34:45 GMT

Is there any pattern to the names? If so, this is easier. If not, you can create a list of the variable names if you include all date variables.

So it depends....

if another_date > max(of date_id--yet_another_date_id) then flag=1;

The question is how to define that list after the OF.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@ChrisTurtle wrote:

After some guidance as to how best to get started. I will have a dataset similar to below where I will have approximately 200 date fields and 100 IDs:

ID date_id another_date_id yet_another_date_id

1 01/01/2019 02/01/2019 05/01/2019

2 02/01/2019 05/01/2019 06/01/2019

I am wanting to know if there is a simpler way to do logical calculations.

e.g. if yet_another_date_id > all other date_ids without typing them all out?

Re: Compare dates within the same record

ChrisTurtle — Wed, 01 Apr 2020 15:37:47 GMT

Thank you I will look at the blog post as there will be no pattern to the names I'm afraid

Re: Compare dates within the same record

unison — Wed, 01 Apr 2020 15:52:14 GMT

I'd approach in this way. This assumes all date columns are named with suffix "date_id":

data have;
input id (date_id another_date_id comparison_date_id) (:date9.);
format date_id another_date_id comparison_date_id mmddyy10.;
datalines;
1 01apr2020 02apr2020 03apr2020
2 01mar2020 15jan2020 10sep2020
3 01apr2020 02apr2020 03apr2020
4 01apr2020 02apr2020 31mar2020
;
run;

*Get date cols into a macro variable for later array reference;
proc sql noprint;
	select 
		name into :datecols 
		separated by " " 
	from dictionary.columns 
	where 
	libname='WORK' and 
	memname='HAVE' and
	upcase(name) like '%DATE_ID' and
	upcase(name) <> 'COMPARISON_DATE_ID'	
	;
quit;

data want;
	set have;
	flag=0;
	array dates &datecols;
	do over dates; /*Define array to search over*/
		if comparison_date_id > dates then do; /*do comparison*/
			flag=1; /*if TRUE then flag*/
			leave; /*leave loop on first TRUE*/
		end;
	end;
run;

Re: Compare dates within the same record

Kurt_Bremser — Wed, 01 Apr 2020 15:57:36 GMT

Once again, the advantage of a long dataset format comes into play:

proc transpose data=have out=long name=datename;
by id;
var _numeric_;
run;

data want;
flag = 1;
do until (last.id);
  merge
    long (
      rename=(col1=_ref)
      where=(datename = "yet_another_date_id")
    )
    long (where=(datename ne "yet_another_date_id"))
  ;
  by id;
  if _ref < col1 then flag = 0;
end;
do until (last.id);
  set long;
  by id;
  if flag then output;
end;
drop _:;
run;

Now you need only write one "variable name".