Search code examples
datesasdate-difference

SAS : Converting Text format to date format and finding difference between dates

I am new to SAS and I have a data set something like below:

Payment 335861  36416   5   41641   41648
Payment 335861  85610   8   41641   41648
Payment 335861  99214   156 41641   41648

In this last two columns are dates in text format, the kind you get when you make a tab delimited file from excel.

Now I want to convert these numbers into date format. and then find the Difference(in days) for the dates thus got. I tried the "informat" way but not getting anywhere with it.Please help....

Solution 
  • data sample_data;
infile cards;
input type $ id amount count tdate1 $ tdate2 $;
cards   ;
Payment 335861  36416   5   41641   41648
Payment 335861  85610   8   41641   41648
Payment 335861  99214   156 41641   41648
;
run;

data out_data;
set sample_data;
format date1 date2 yymmdds10.; /* format=display number (of days) as DATE */
date1 = input(tdate1, 5.);/* conversion from text to number 
                (assuming it's a SAS date value=number of days from 1jan1960 */
date2 = input(tdate2, 5.);
datediff = date1 - date2; /* calculate date difference */
run;

    Example assumes your "dates" come from SAS, meaning it's a number of days since 1jan1960. This will mean value '41641' is 3jan2074.

    In case numbers come from a different data source and the meaning is different you'll have to do some correction to the numbers/dates.

    E.g. in excel '41641' is 2.1.2014 (it's number of days since 1jan1900 + 1). The conversion + adjustment needed in that case would be:

    date1 = input(tdate1, 5.) - ('1jan1960'd - '1jan1900'd) - 2;
date2 = input(tdate2, 5.) - ('1jan1960'd - '1jan1900'd) - 2;