I am using DATEDIF to get the difference between two sets of dates for customers. Some of the customers don't have both dates so I am checking for that before I use DATEDIF. When a customer does not have both dates I want it to be returned as null (or MISSING) as 0 days has significance.

My code is below, both DATEDIFs are giving me strange values when DATE2 is missing, but correct values when it exists:

TABLE FILE 
PRINT
	REGION_NAME
	COMPUTE DAYS_BETWEEN/D20 MISSING ON = IF DATE1 IS-NOT MISSING AND DATE2 IS-NOT MISSING THEN DATEDIF(DATE1, DATE2, 'D') ELSE MISSING;
	COMPUTE DAYS_SINCE_LAST/D20 MISSING ON = IF DATE2 IS-NOT MISSING THEN DATEDIF(DATE2,TODAY,'D') ELSE MISSING;
BY SUBJECT_ID 

Thanks,
Cody

This message has been edited. Last edited by: Cody,

Cody,

You DEFINE'd the columns as D20 MISSING ON. You can't have an ALPHA in a NUMERIC:

  
COMPUTE DAYS_BETWEEN/D20 MISSING ON = IF DATE1 IS-NOT MISSING AND DATE2 IS-NOT MISSING THEN DATEDIF(DATE1, DATE2, 'D') ELSE 0;
COMPUTE DAYS_SINCE_LAST/D20 MISSING ON = IF DATE2 IS-NOT MISSING THEN DATEDIF(DATE2,TODAY,'D') ELSE 0;

Hi Tom,

I tried changing things up to what you had and I still got strange values. It seems that even though there was no DATE2 for some records a value of some sort was coming over (perhaps as a result of a join that I am using to get the data originally). To fix this I used:

  
COMPUTE DAYS_BETWEEN/D20 MISSING ON = IF DATE1 GT '2000/01/01' AND DATE2 GT '2000/01/01' THEN DATEDIF(DATE1, DATE2, 'D') ELSE 0;
COMPUTE DAYS_SINCE_LAST/D20 MISSING ON = IF DATE2 GT '2000/01/01' THEN DATEDIF(DATE2,TODAY,'D') ELSE 0;

2000/01/01 being a date that is well before any of the dates I will have in the results

Cody,

Try this:

  
COMPUTE DAYS_BETWEEN/D20 MISSING ON    = IF DATE1 IS MISSING THEN 0 ELSE 
                                         IF DATE2 IS MISSING THEN 0 ELSE 
                                         IF DATE1 GE '2000/01/01' AND 
                                         DATE2 GE '2000/01/01' THEN DATEDIF(DATE1, DATE2, 'D') ELSE 0;
COMPUTE DAYS_SINCE_LAST/D20 MISSING ON = IF DATE2 IS MISSING THEN 0 ELSE DATEDIF(DATE2,TODAY,'D');

Your TODAY value also needs to be fomatted correctly...

This message has been edited. Last edited by: Tom Flynn,

Hi Tom,

I resolved this with:

  
COMPUTE DAYS_BETWEEN/D20 MISSING ON = IF DATE1 GT '2000/01/01' AND DATE2 GT '2000/01/01' THEN DATEDIF(DATE1, DATE2, 'D') ELSE MISSING;
COMPUTE DAYS_SINCE_LAST/D20 MISSING ON = IF DATE2 GT '2000/01/01' THEN DATEDIF(DATE2,TODAY,'D') ELSE MISSING;

I couldn't have 0s when no value was returned because 0 has a different meaning than missing in this particular case.

Hi Cody,

Excellent! I haven't used the MISSING process so I learned something as well.

Thanks!

Just a note: if using smart dates with MISSING=ON, you can get differences simply by subtracting:

DEFINE FILE CAR                                                        
MYDA/YYMD MISSING ON = IF COUNTRY EQ 'ENGLAND'  THEN 'JAN 11, 2010' ELSE
                       IF COUNTRY EQ 'FRANCE'  THEN MISSING  ELSE      
                       'JAN 11, 2010' ;                                
MYDB/YYMD MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN MISSING        ELSE
                       IF COUNTRY EQ 'FRANCE'  THEN 'JAN 21, 2010' ELSE
                       'JAN 21, 2010' ;                                
DIFF/I5 MISSING ON NEEDS ALL = MYDB - MYDA ;                           
END                                                                    
TABLE FILE CAR                                                         
PRINT MYDA MYDB DIFF                                                   
BY COUNTRY                                                             
END                                                                    
  

To get:

COUNTRY     MYDA        MYDB         DIFF  
-------     ----        ----         ----  
ENGLAND     2010/01/11  .               .  
FRANCE      .           2010/01/21      .  
ITALY       2010/01/11  2010/01/21     10  
JAPAN       2010/01/11  2010/01/21     10  
W GERMANY   2010/01/11  2010/01/21     10