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

saskapa — Wed, 04 Feb 2015 13:17:42 GMT

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

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

RW9 — Wed, 04 Feb 2015 13:29:45 GMT

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.

topic Re: Min and max dates with proc sql date type conflictsconvert all the in SAS Procedures

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

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