should the date in the intnx function be in date format?

[lixuan]

Hi, I test the the numeric format and the date format used in intnx funtion, and found the results are  the same . why? if it's meanless , what is the usage of date formate?  I also  dont understand  the underlying rule of  intnx function, how  could it realize the time without date format? Thanks & regards.

[Kurt_Bremser]

The formats control how values are displayed, not how they are calculated. The functions always work with the raw values.

[lixuan]

OK, I see ,then It's unnecessary to change the formate before I use the intnx funtion. Thank you 

[RW9]

This is basic SAS knowledge:

Dates and times are numbers since a certain timepoint.  A date is the number of days since 01Jan1960, a time is seconds from midnight.  To display that number in a meaningful way to the user you apply a format to it so that it displays in the way the user expects to see it.  All formula work regardless of that however so:

days=today() - '01jan2017'd;

and

days=intck('days','01jan2017'd,today());

Are functionally equivalent, however in most circumstances it is clearer to just use the functions all the time, especially with things like months/years with leap years and differeing days in each month and such like.

[lixuan]

Thanks, I ask the question because i encount a very strange thing. I used the intnx funtion like this as a 'where' conditon of sql join. The reult should be that  'b.yymm' is in the past 36 months of 'a.yymm', but some results exceeded 36 months. I dont know what happened. the veariables a.yymm and b.yymm had been  both producted by 'yymm=100*year()+month()' and the format is best12. could you help me or give me some hints? thanks a lot .

intnx('month',a.yymm,-36)<=b.yymm<=a.yymm

[RW9]

Post test data in the form of a datastep!!

We cannot see what you are working with.

Do you have YYMM format data?  if so then use input(cats(datevariable,"01"),yymmdd10.) to get a numeric SAS date, then format it as you want to see it.  I suspect your YYMM variables are not right, but I can't tell as am not looking at your machine.

[Astounding]

As far as SAS is concerned, the variables you are working with are not dates at all.  Dates refer to a specific day on SAS's date scale.  (Refer to @RW9 post above for the very basic details of that.)  Your variables are simply integers to SAS.  But the good news is that these integers are easy to work with if you are referring to a time period 36 months earlier.  You could simply use:

(a.yymm - 300) <= b.yymm <= a.yymm

Of course, this won't screen out illegal values (such as b.yymm = 1515), but that won't occur if your variables were created by the formulas that you mention.

[Kurt_Bremser]

The intnx function (read the documentation here: http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p10v3sa3i4k...) allows a fourth parameter that controls the positioning of the result within the target month: beginning, middle, end, same. This might influence your comparisons

And if the yymm variables are still constructed in the extremely unusable way you mentioned, they can't be used directly with date-related functions.

STORE DATE VALUES AS SAS DATE VALUES!!!, and not in some human-readable, but otherwise utterly useless form.

[Patrick]

@lixuan

the veariables a.yymm and b.yymm had been  both producted by 'yymm=100*year()+month()' 

The formula you've posted is certainly not complete and would throw a syntax error. Is it eventually something like:

yymm=100*year(today()) + month(today());

If so then the values in variable yymm are NOT SAS Date values. SAS calendar functions like intnx() require SAS Date/DateTime values. Read carefully what's in below link as you need to understand this concept.

http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p1wj0wt2ebe2a0n1lv4l...

If you want a variable populated with the first of the current month then the formula would need to look like:

yymm=intnx('month',date(),0,'b');

[lixuan]

OK, here is my code. As you can see , before merge I format  both variables to date format which is in best12. fomat originally. But I get the result as following. the result with red line is obviously wrong.

data m_fegg;
set ibes.m_fegg;
cusip=substr(cusip,1,6);
format yymm yymmn6.;
run;
data sdc_final;
set sdc.sdc_final;
format yymm_a yymm_e yymmn6.;
run;
/*create the date before m&a*/
proc sql;
create table m_i_s1 as select b.*,a.yymm_a
from sdc_final as a, 
	 m_fegg as b
where intnx('month',a.yymm_a,-36)<=b.yymm<=a.yymm_a
	 & a.acq_cusip=b.cusip;
quit;
data m_i_s1;
set m_i_s1;
format yymm_a yymm best12.;
run;

[lixuan]

Where is my result? I posted a picture.

[lixuan]

[Tom]

Looks like your YY.... variables do not contain dates, just integers.

The difference between the numbers 200,403 and 199,812 is only 519.

Which is less than the number of days in 36 months.

[Kurt_Bremser]

This

data sdc_final;
set sdc.sdc_final;
format yymm_a yymm_e yymmn6.;
run;

just assigns another format. It does NOT change the values, and is therefore useless when you want to use your variables with date functions.

Convert to real SAS date values, as I have already shown you in another thread of yours.

Until you do that, all discussion regarding use of date functions is a waste of time.

[Kurt_Bremser]

Just to illustrate your mistakes concerning data values and their formats:

data mydates;
input yymm;
cards;
199812
200403
;
run;

data play_around;
set mydates;
date_wrong = put(yymm,yymmn6.);
date_correct = input(put(yymm,6.) !! '01',yymmdd8.);
format date_correct date9.;
period = put(date_correct,yymmn6.);
date_correct_raw = date_correct;
format date_correct_raw best6.;
surprise = intnx('month',yymm,-36);
format surprise date9.;
run;

Start reading up on the internals of SAS date and time values:

https://support.sas.com/resources/papers/proceedings15/1334-2015.pdf