Search code examples
datesascompare

SAS comparing two dates

I have a sas file which looks like

date1       date2       date3       comp

20201208    20110201    20120201  
20201208    20200101    20210201

all date are numeric with format yymmddn8. and comp is a text type.

Now I want to fill comp with 1 or 0 like

if date2 < date1 < date3 then comp=1; else comp=0;

but it doesn't work. I always get 1 even if the condition is true.

Progam (from comments)

data kl.kl_neukunde; 
  set kl.kl01; 
  format tday yymmddn8. ; 
  format date_nk yymmddn8. ; 
  format date_nk_12mon yymmddn8. ; 
  tday=today(); 
  date_nk = put(input(erste_besuch, ddmmyy10.), yymmdd8.)*1;
  date_nk_12mon = date_nk + 10000; 
  if date_nk='' then date_nk=0; 
  if date_nk_12mon='' then date_nk_12mon=0;
  if date_nk < tday < date_nk_12mon then neukunde="1";
Solution

  • If you are going to work with dates – you should learn how to use SAS dates as there are a lot of great functions that help with date handling. What you are doing now is the hard way to work with dates. A nice paper to learn about dates is: https://support.sas.com/resources/papers/proceedings/proceedings/sugi25/25/btu/25p058.pdf

    For your specific SAS code you have two problems.

    One problem is with variable date_nk. The format used with the put function is missing an “n”. The format yymmdd8. uses a dash to separate date parts so when that is multiplied by 1 to create a numeric variable the value becomes missing. This will create a non-missing value: date_nk = put(input(erste_besuch, ddmmyy10.), yymmddn8.)*1;

    However, once date_nk is created like that, it is numeric but NOT a SAS date. So the bigger problem is when you compare it with the tday variable which IS a SAS date.

    See if this SAS code below makes sense:

    data want;
  erste_besuch = '30-06-2020';

  format tday yymmddn8. ; 
  format date_nk yymmddn8. ; 
  format date_nk_12mon yymmddn8. ; 

  *** THIS IS A SAS DATE ***;
  tday=today(); 

  *** THESE ARE NUMERIC BUT -NOT- SAS DATES - SO USING DATE FORMATS WITH THESE VARIABLES IS A PROBLEM ***;
  *date_nk = put(input(erste_besuch, ddmmyy10.), yymmdd8.)*1;
  *date_nk = put(input(erste_besuch, ddmmyy10.), yymmddn8.)*1;
  *date_nk_12mon = date_nk + 10000; 

  *** THESE ARE SAS DATES ***;
  date_nk = input(erste_besuch, ddmmyy10.);
  date_nk_12mon = intnx('month', date_nk, 12, 'same');

  *** IF DATE VARIABLES ARE MISSING, KEEP AS MISSING, DO NOT RESET TO ZERO ***;
  *if date_nk='' then date_nk=0; 
  *if date_nk_12mon='' then date_nk_12mon=0;

  *** NOW YOU HAVE 3 VARIABLES THAT ARE ALL SAS DATES AND CAN COMPARE THEM CORRECTLY ***;
  if date_nk < tday < date_nk_12mon then neukunde="1"; 
run;

title 'NEW ATTEMPT';
proc print data=want;
run;
title2 'WITHOUT FORMATS';
proc print data=want;
    format _all_;
run;