topic Re: Not able to do date difference in New SAS User

Not able to do date difference

ash3 — Wed, 26 May 2021 12:30:52 GMT

starting_ date	last _date	difference
Dec-11	Jan-15	.
Dec-11	Apr-13	.
Dec-11	Jan-16	.

data want;
set have ;
difference = intck("month",starting_date,last_date);
run;

I am getting missing values in difference column using the above code. please help me how I get difference between starting_date and last_date

Re: Not able to do date difference

PaigeMiller — Wed, 26 May 2021 12:36:53 GMT

Dec-11 is not a date, because in SAS, a date is an integer representing the number of days since 01JAN1960. Furthermore, you may have a formatted date, in which case Dec-11 might be a formatted date representing 01DEC2011, but that's a wild guess, and it could be a lot of other things.

To further debug this situation, you would need to provide a portion of your original data following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Then we can be sure that we have a complete and unambiguous understanding of what Dec-11 represents, and so we can then help further.

Re: Not able to do date difference

ash3 — Wed, 26 May 2021 12:48:53 GMT

So how do I convert Dec-11 to 01Dec2011?

Re: Not able to do date difference

PaigeMiller — Wed, 26 May 2021 12:54:21 GMT

I don't know what Dec-11 means, I don't know if this is formatted, and you need to provide a portion of your actual data following the instructions I gave. This will clear up the matter unambiguously.

Re: Not able to do date difference

singhsahab — Wed, 26 May 2021 13:00:13 GMT

@ash3 You have to process it using conditional statement and character function.

Assuming "starting_date" and " last_date" contains Month and Year.

steps you can follow.

1. Create two column with the delimiter "-" for starting_date as Month and Year column. (Same process for for last_date).

2. For Month column apply If statement like if Month eq 'Jan' then Month_1=01; (for all the months)

3. Concatenate it as '01'(hard coded) -> cats('01','-',Month_1,'-',Year);

3. convert Final variable in Num type and apply your difference logic.

Thanks.

Re: Not able to do date difference

ash3 — Wed, 26 May 2021 13:01:37 GMT

Variable Type Len Format Informat
starting_date Char 6 $6. $6.
last_date Char 6 $6. $6.

Re: Not able to do date difference

PaigeMiller — Wed, 26 May 2021 13:11:41 GMT

So you don't have SAS dates, you have a character string that looks like a date to humans. This is something you have to know before you begin trying to write a program because the function INTCK will not work on character strings. The INPUT function will convert character strings to actual numeric SAS dates, and then INTCK will work on the actual numeric SAS dates.

data have;
    date1='Dec-11';
    date2='May-15';
run;

data want;
    set have;
    ndate1=input(compress(date1,'-'),monyy.);
    ndate2=input(compress(date2,'-'),monyy.);
    difference=intck('month',ndate1,ndate2);
run;

Re: Not able to do date difference

ash3 — Wed, 26 May 2021 13:20:24 GMT

Thank you very much