BookmarkSubscribeRSS Feed
ash3
Calcite | Level 5
starting_ datelast _datedifference
Dec-11Jan-15.
Dec-11Apr-13.
Dec-11Jan-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

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ash3
Calcite | Level 5
So how do I convert Dec-11 to 01Dec2011?
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ash3
Calcite | Level 5
Variable Type Len Format Informat
starting_date Char 6 $6. $6.
last_date Char 6 $6. $6.
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
ash3
Calcite | Level 5
Thank you very much
singhsahab
Lapis Lazuli | Level 10

@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.

SS

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 656 views
  • 0 likes
  • 3 in conversation