SAS INTNX for Missing date

Background Story:

Missing dates are something the pharmaceutical industry deals with on a daily basis. There are some common methods.

1. Completely missing year, month, and day then no imputation.
2. Set a certain number( ex.Jan01) for missing month and day
3. Compare with treatment start date, and manipulate accordingly.
   If the AE start date and the treatment start date have the same year and month then we set the missing AE start date the same as the treatment start date.
   if the AE start date and the treatment start date have the same year, the AE start month is later than the treatment start month. we set the AE start date to be the first day of the month(xx-01).
   if the AE start date is earlier than the treatment starting month. We set AE start day to be the last day of the month(xx-31/30/29/28)

As we can see, the 3rd method is the best model, however, it is a little complicated to implement it. Because we have to consider different year and month when it comes to the last day of the month.

Luckily we have INTNX function.

intnx("time scale",variable, relation, "time position");
a=intnx("month", trtsdt, 1, "beginning");
* variable a = the beginning of next month of trtsdt.
we can use 1,2,3,4 ... to present the number we want to add on "time scale"

Goal: Imputate Partial Missing AE date.

Key SAS Function: INTNX, Year, Month, Input

Sample Code:

data ae_date;
set a;
if length(compress(aestdtc))=10 then aestdt=input(aestdtc,yymmdd10.);
*Missing day;
if aestdtc ne " " and trtsdt> . and aestdt=. and amonth=tmonth and ayear=tyear 
and length(compress(aestdtc))=7 
then aestdt=trtsdt;
if aestdtc ne "" and trtsdt>. and aestdt=. and length(compress(aestdtc))=7 then do;
sampleae=input(substr(aestdtc,1,7)||"-01",yymmdd10.);
if (ayear>tyear) or (ayear=tyear and amonth>tmonth) then aestdt=intnx("month",sampleae,0,"end");
else if (ayear<tyear) or (ayear=tyear and amonth<tmonth) then astdt=intnx("month",sampleae,0,"beginning");
end;
*Missing Month and Day;
if aestdtc ne " " and trtsdt> . and aestdt=. and ayear=tyear 
and length(compress(aestdtc))=4
then aestdt=trtsdt;
if aestdtc ne "" and trtsdt>. and aestdt=. and length(compress(aestdtc))=4 then do;
if (ayear<tyear) then aestdt=input(substr(aestdtc,1,4)||"-12-31",yymmdd10.);
else if ayear>tyear then aestdt=input(substr(aestdtc,1,4)||"-01-01",yymmdd10.);
end;
run;

Sample Output:

Happy Studying!