topic Re: Choose the earlist date from five date variables in SAS Programming

Choose the earlist date from five date variables

L777 — Mon, 19 Jun 2023 19:13:44 GMT

How can I choose the earliest date from a list of variables, and create a new variable save the earliest date. The missing values are saved as "." for these 5 variables.

The missing values should be ignored when comparing. Only if 5 date variables are all missing, the new variable will be saved as a missing value.

The code I wrote:

data df1;

set df;

earliest=min(of date_a date_b date_c date_d date_e);

format earliest mmddyy10.;

run;

The new variable was created, but all the values are missing.

Re: Choose the earlist date from five date variables

PaigeMiller — Mon, 19 Jun 2023 19:23:56 GMT

Sometimes, all you need to do is check the documentation for the MIN function. It specifically answers your question:

"The MIN function returns a missing value (.) only if all arguments are missing."

Since you said: "The new variable was created, but all the values are missing" then your data must have problems in it. Show us data set DF as working SAS data step code (instructions and examples) and not in any other form. Are there errors or warnings or other problems in the log?

Re: Choose the earlist date from five date variables

Reeza — Mon, 19 Jun 2023 19:18:55 GMT

Not sure you're using OF correctly here.

earliest=min(date_a, date_b, date_c, date_d, date_e);

Does that also return the same results? If so, something is either wrong with the data or logic.

Re: Choose the earlist date from five date variables

L777 — Mon, 19 Jun 2023 20:27:37 GMT

I tried that. But there is an error: Expecting an arithmetic operator if not include "of".

Re: Choose the earlist date from five date variables

L777 — Mon, 19 Jun 2023 20:34:48 GMT

There are no warnings or errors in log. But there are some note like:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
16:27 16:48 16:67 16:88 16:105
NOTE: Invalid numeric data, rx_summ_dt_radiation='9/17/2013' , at line 16 column 27.
NOTE: Invalid numeric data, rx_summ_date_chemo='5/10/2013' , at line 16 column 48.
NOTE: Invalid numeric data, rx_summ_date_hormone='8/26/2013' , at line 16 column 67.
NOTE: Invalid numeric data, rx_summ_date_transplnt_endocr_da='0/0/0' , at line 16 column 105.

Do I need to convert the format of the date before using the min function?

Re: Choose the earlist date from five date variables

Reeza — Mon, 19 Jun 2023 20:40:30 GMT

Yes, you have character variables. Dates need to be numeric before they can be 'ordered'.

Rough idea - you'll need to list out your variables yourself.

data want;
set have;

array _old(*) $10. rx_summ_dt_radiation ....;
array _new(*) dt_radiation dt_chemo ...;

do i=1 to dim(_old);
_new(i) = input(_old(i), mmddyy10.);
end;

format dt_: date9.;
run;

Re: Choose the earlist date from five date variables

PaigeMiller — Mon, 19 Jun 2023 21:05:19 GMT

@L777 wrote:

There are no warnings or errors in log. But there are some note like:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
16:27 16:48 16:67 16:88 16:105
NOTE: Invalid numeric data, rx_summ_dt_radiation='9/17/2013' , at line 16 column 27.
NOTE: Invalid numeric data, rx_summ_date_chemo='5/10/2013' , at line 16 column 48.
NOTE: Invalid numeric data, rx_summ_date_hormone='8/26/2013' , at line 16 column 67.
NOTE: Invalid numeric data, rx_summ_date_transplnt_endocr_da='0/0/0' , at line 16 column 105.

Do I need to convert the format of the date before using the min function?

To use correct SAS terminology, this is not a "format" issue at all. It is the fact that dates need to be numeric, not character. This is the variable "type" and not "format". In SAS, there are only two variable types, numeric and character.

Re: Choose the earlist date from five date variables

SASKiwi — Mon, 19 Jun 2023 21:11:30 GMT

Quoting error messages without the code that caused it isn't useful. So we can see what is happening you need to post the COMPLETE SAS log of your DATA step containing both code and any errors or notes.

Re: Choose the earlist date from five date variables

L777 — Mon, 19 Jun 2023 22:06:29 GMT

I tried this code, but there is warning:

NOTE: Invalid argument to function INPUT at line 12 column 11.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

Re: Choose the earlist date from five date variables

L777 — Mon, 19 Jun 2023 22:20:08 GMT

I finally create the new variable based on your code. Thank you!

Re: Choose the earlist date from five date variables

Reeza — Mon, 19 Jun 2023 22:21:42 GMT

Please post the full code used and the log. If you have some variables as dates and some as character that's also one possible issue.

Re: Choose the earlist date from five date variables

Reeza — Mon, 19 Jun 2023 22:27:39 GMT

Glad it worked for you!