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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.