How do I get proc SQL to select minimum date even when minimum date is missing.

[inason]

In the data below I would like proc sql to select the minimum date for subject 123 as the missing date.

data visit;

input subject $1-3  dtc $4-24 ;

cards;

123  2014-01-15T00:00

123 

123  2014-01-17T00:00:00

124  2014-01-15T00:00:00

124  2014-01-15T00:00:00

124  2014-01-17T00:00:00

;

run;

proc sql;
create table want. as
select distinct subject, min(dtc) as mindt format = date9.
from have
where subject ne ''
group by subject;
quit;

[Patrick]

Read your source data in a way so the datetime string gets converted to a SAS datetime value.

The SAS min() function excludes missings.

Below one possible approach.

data have;
  infile datalines truncover;
  input subject $1-3 @4 dtc :e8601dt19.;
  format dtc datetime20.;
  cards;
123  2014-01-15T00:00
123 
123  2014-01-17T00:00:00
124  2014-01-15T00:00:00
124  2014-01-15T00:00:00
124  2014-01-17T00:00:00
;

proc sort data=have out=inter;
  where subject ne ' ';
  by subject dtc;
run;

data want;
  set inter;
  by subject;
  if first.subject;
run;

[Ksharp]

data have;
  infile datalines truncover;
  input subject $1-3 @4 dtc :e8601dt19.;
  format dtc datetime20.;
  cards;
123  2014-01-15T00:00
123 
123  2014-01-17T00:00:00
124  2014-01-15T00:00:00
124  2014-01-15T00:00:00
124  2014-01-17T00:00:00
;

proc sql;
create table want as
select distinct subject, dtc as mindt format = dtdate9.
from have
group by subject
having coalesce(dtc,-9999999)=min(coalesce(dtc,-9999999))
;
quit;

[FreelanceReinh]

Hello @inason,

If you want MINDT to be a SAS date value (as suggested by your format = date9. specification), you can use a CASE expression:

proc sql;
create table want as
select subject, case when nmiss(dtc) then . else min(input(dtc,yymmdd10.)) end as mindt format=date9.
from visit
where subject ne ' '
group by subject;
quit;

Edit: Or, a bit shorter, replace the above CASE expression by an IFN function call:

ifn(nmiss(dtc),.,min(input(dtc,yymmdd10.)))