Min and max dates with proc sql date type conflictsconvert all the

[saskapa]

Hi,

I have a little issue and I can't clear in how to resolve it.

lets say that I have the following data set :

data visit;

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

cards;

123  2014-01-15

123  2014-01-15T00:00

123  2014-01-15T00:00:00

;

run;

I would like to calculate the minimum date for this subject. In order to do so I have to convert date into a datetime type in order to compare them.

data test_v; set visit ;

/*create a new dtc_up that will complete dtc*/

if count(dtc,':') eq 2 then dtc_up=dtc;

if count(dtc,':')        eq 1 then dtc_up=compress(dtc!!":00");

else if count(dtc,':') eq 0 then do ;

           if count(dtc,'-')        eq 2                                 then dtc_up=compress(dtc!!"T00:00:00");

           else if count(dtc,'-') eq 1                                 then dtc_up=compress(dtc!!"-01T00:00:00");

           else if count(dtc,'-') eq 0  and not missing(dtc) then dtc_up=compress(dtc!!"01-01T00:00:00");

          else call missing(dtc_up);

end;

if not missing(dtc_up)  then dt_anydtm=input(dtc_up,anydtdtm.);

else call missing(dt_anydtm);

run;

Once I have done this I use a proc sql statement to compute the minimum:

proc sql;

create table min as

select subject, min(dt_anydtm) as svstdtc format=IS8601DT.

from test_v

group by subject;

quit;

Problem, from which record is the minimum value taken from ????  From record 1, from record 2 or from record 3 ? I need to know that because I'll create a new char variable called SVSTDTC which will capture the original minimum value.

For those who are familiar to CIDSC I am trying to bild an SV domain.

By the way can SAS compare 2014-01-15  with 2014-01-15T00:00 without performing the datastep above  and decide which is the minimum value ?

Thanks in advance,

Saskap

[RW9]

Well, firstly your code can be shrunk down into the one proc sql:

proc sql;

  create table WANT as

  select  MIN(DTC) as MIN_DTC format=datetime.,

          MAX(DTC) as MAX_DTC format=datetime.

  from    (select case when length(strip(DTC))=10 then input(strip(DTC)||"T00:00:00",e8601dt.)

                       when length(strip(DTC))=16 then input(strip(DTC)||":00",e8601dt.)

                       else input(DTC,e8601dt.) end as DTC from VISIT);

quit;

As for what to do about comparisons, this is really up to what your company/project team decide.  There are several options:

Only compare where full date and time present.

Only compare dates.

Compare date/times and populate time with a fixed timepoint if missing.

We can't really tell you which one to choose as that would be on a per-study basis.

As for CDISC - SDTM, sorting and such like is done on the text value as it is.  For Adam, then it should be per the rules in you stats plan.