topic Re: Flag Last Assay Date Prior to Exposure Date in SAS Programming

Flag Last Assay Date Prior to Exposure Date

mglogan — Sat, 26 Jan 2019 19:38:15 GMT

Hello,

I need to create a new variable that flags last assay date prior to exposure date.

it seems simple, but I am unsure of the best method?

can you provide some suggestions?

thank you in advanced!

Sample data:

SUBJECT ASSAY_NAME ASSAY_DATE EXPOSURE_DATE WANT: BASELINE

001 Alanine Aminotransferase 2017-07-05T12:01 2017-07-19T09:55
001 Alanine Aminotransferase 2017-07-19T09:22 2017-07-19T09:55 Y
001 Albumin 2017-08-15T09:38 2017-07-19T09:55
001 Albumin 2017-08-30T09:09 2017-07-19T09:55
001 Albumin 2017-07-15T08:10 2017-07-19T09:55 Y

Re: Flag Last Assay Date Prior to Exposure Date

novinosrin — Sat, 26 Jan 2019 19:51:14 GMT

Are your dates numeric SAS dates?

Re: Flag Last Assay Date Prior to Exposure Date

novinosrin — Sat, 26 Jan 2019 20:16:13 GMT

The logic is very simple

Let me know if you can understand the following

proc sql;
create table want(drop=dif) as
select *, ifn(EXPOSURE_DATE-ASSAY_DATE<0,.,EXPOSURE_DATE-ASSAY_DATE) as dif,ifc(min(calculated dif)=calculated dif,'Y',' ') as want
from have
group by subject,ASSAY_NAME	;
quit;

Re: Flag Last Assay Date Prior to Exposure Date

mglogan — Sat, 26 Jan 2019 20:16:47 GMT

Hmm so, the dates are character format in this example. I have a previous date vale in sas numeric format but not the time. However, I can create numeric sas date time values.

Re: Flag Last Assay Date Prior to Exposure Date

Tom — Sat, 26 Jan 2019 20:19:30 GMT

Here is an simple way. Just sort the data by descending date and flag the first one in the group.

proc sort data=have ;
  by SUBJECT ASSAY_NAME    EXPOSURE_DATE  descending ASSAY_DATE ;
run;

data want ;
  set have;
  by SUBJECT ASSAY_NAME    EXPOSURE_DATE ;
  if first.expose_date then BASELINE='Y';
run;

Re: Flag Last Assay Date Prior to Exposure Date

novinosrin — Sat, 26 Jan 2019 20:19:49 GMT

Hi @mglogan Here is a test with taking sample as numeric sas dates as yymmdd10.

data have;
input SUBJECT   ASSAY_NAME   & $40.                   ASSAY_DATE  : yymmdd10.        EXPOSURE_DATE : yymmdd10.;  
format 	ASSAY_DATE EXPOSURE_DATE yymmdd10.;
cards;
001             Alanine Aminotransferase     2017-07-05T12:01    2017-07-19T09:55
001             Alanine Aminotransferase     2017-07-19T09:22    2017-07-19T09:55     Y
001             Albumin                                 2017-08-15T09:38    2017-07-19T09:55
001             Albumin                                 2017-08-30T09:09    2017-07-19T09:55
001             Albumin                                 2017-07-15T08:10    2017-07-19T09:55 
;

proc sql;
create table want(drop=dif) as
select *, ifn(EXPOSURE_DATE-ASSAY_DATE<0,.,EXPOSURE_DATE-ASSAY_DATE) as dif,ifc(min(calculated dif)=calculated dif,'Y',' ') as want
from have
group by subject,ASSAY_NAME	;
quit;

Feel free to to use an order by clause after group by to get the order the way want.

Re: Flag Last Assay Date Prior to Exposure Date

Tom — Sat, 26 Jan 2019 20:21:24 GMT

@mglogan wrote:

Hmm so, the dates are character format in this example. I have a previous date vale in sas numeric format but not the time. However, I can create numeric sas date time values.

If the dates are as in your post it doesn't matter if they are valid datetime values with an ISO format attached to make them print like that or just character strings in that ISO format. Either way they will sort the same since the lexical ordering of that string is the same as the numeric ordering of the dates they represent.

Re: Flag Last Assay Date Prior to Exposure Date

mglogan — Sat, 26 Jan 2019 22:02:24 GMT

So I noticed that I will need to include time in the calculation, since there are assays performed on the day of exposure but prior to exposure time. In this case can I use the raw sas datetime value or do I need to apply a datetime format?

thank you!

Re: Flag Last Assay Date Prior to Exposure Date

mglogan — Sat, 26 Jan 2019 22:05:57 GMT

Thank for the quick response! I'll try this method too.

Re: Flag Last Assay Date Prior to Exposure Date

novinosrin — Sat, 26 Jan 2019 22:46:07 GMT

No need to apply format. As long as your date or datetime value are numbers, the code will comfortably work

Re: Flag Last Assay Date Prior to Exposure Date

mkeintz — Sat, 26 Jan 2019 23:43:28 GMT

If your data are sorted by subject/assay_name/assay_date, the a single data step can do what you need:

data want (drop=nxt_:);
  set have (keep=subject assay_name);
  by subject assay_name ;

  merge have
        have (firstobs=2 keep=assay_date rename=(assay_date=nxt_assay_date));

  if assay_date<exposure_date and
     (last.assay_name=1 or nxt_assay_date>exposure_date)
  then flag='Y';
run;

The set statement only reads the BY variables subject and assay_name. This provides the automatic dummy variable last.assay_name which is used later to test whether the record-in-hand is the last record for that subject/assay_name combination.
The MERGE statement provide a way to simultaneously read the current record and the next record (the assay_date variable only, renamed to nxt_assay_date). This allows a test to determine whether the current record is the last one with assay_date < exposure_date.

Re: Flag Last Assay Date Prior to Exposure Date

mglogan — Wed, 30 Jan 2019 17:35:59 GMT

Hello everyone, thank you for your suggestions.

After looking at my data, it turns out that I will need to flag baseline records by subject, test (i.e. glucose, albumin etc.) and category (chemistry, hematology, urinalysis, serology etc.), Date values are in character format.

My output sample data is attached. The code I am working on is below, but it doesn't quite do what I want. It is flagging all dates prior to exposure by test category, but I only need the latest (or first.lbcat), and I have not been able to get it to flag correctly.

data lbflag; 
  set _lbflag;
  by usubjid lbtest lbcat descending lbdtc;
  retain flag;
  if first.lbtest then flag=0;

  *** flag test dates prior to exposure ;
  if flag=0 and lbdtc <= rfxstdtc and lbstat ~='NOT DONE' then flag=1 ; 

  *** if test is prior to exposure, flag by lbcat ;
  if flag=1 then do;
  retain _flag ;
  if first.lbcat then _flag=0;
   if lbdtc <= rfxstdtc
	then do ;
	 _flag=1 ;
	 lbblfl = 'Y' ;
        end;
     end;
run;