07-24-2017 05:35 AM
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.
07-25-2017 09:47 AM - edited 07-25-2017 09:49 AM
Given your input data as posted, you won't get any results because there are no matching cusip's in your datasets.
And that's the complete code I ran, for reference:
data WORK.SDC_FINAL; infile datalines dsd truncover; input yymm_a:32. y_a:32. yymm_e:32. y_e:32. acq_cusip:$30. Acquiror_Name:$60. acq_sic:32. target_sic:32. cash_p:$18.; label Acquiror_Name="Acquiror Name"; datalines4; 200204,2002,200204,2002,00013L,APS Healthcare Bethsda,8099,8099,0 199312,1993,199401,1994,00026R,A-OK(GB-Inno-BM SA),5251,5251,0 200607,2006,200609,2006,00030F,AMI Semiconductor Inc,3674,3674,1 199106,1991,199106,1991,000352,AAI Corp,3812,3699,1 199201,1992,199201,1992,000352,AAI Corp,3812,3829,0 200611,2006,200611,2006,000352,AAI Corp,3812,3679,0 200802,2008,200803,2008,000361,AAR Corp,3724,4581,0 201003,2010,201004,2010,000361,AAR Corp,3724,4522,0 199710,1997,199711,1997,00074C,American Builders,5033,5033,0 199504,1995,199505,1995,000752,ABC Rail Products Corp,3469,4789,1 199704,1997,199707,1997,00075X,ABC Radio Networks,4832,4832,1 199805,1998,199805,1998,00077R,ABR Information Services Inc,7374,7389,0 199411,1994,199503,1995,000781,ABS Industries Inc,5085,3714,0 199401,1994,199403,1994,000872,ACS Enterprises Inc,4841,4841,1 199808,1998,199808,1998,00087X,ACSYS Inc,7363,7361,0 199603,1996,199603,1996,000886,ADC Telecommunications Inc,3669,1731,0 199801,1998,199801,1998,000886,ADC Telecommunications Inc,3669,5731,0 199905,1999,199906,1999,000886,ADC Telecommunications Inc,3669,3674,0 200403,2004,200405,2004,000886,ADC Telecommunications Inc,3669,3663,0 200710,2007,200712,2007,000886,ADC Telecommunications Inc,3669,4812,0 ;;;; run; data WORK.M_FEGG; infile datalines dsd truncover; input OFTIC:$6. CUSIP:$8. CNAME:$16. MEANEST:32. yymm:32. g:32. g_indavg:32.; label OFTIC="Official Ticker Symbol" CUSIP="CUSIP/SEDOL" CNAME="Company Name" MEANEST="fe_t+1" g="g"; datalines4; SODA,00020910,A & W BRANDS,0.45,198803,20,20 AAON,00036020,AAON,0.05,199512,17,14.239545455 ABB,00037520,ABB,1.25,201406,11.6,12.135 ABCB,00040010,ABC BANCORP,0.64,199806,12,13.100625 ABCR,00075210,ABC RAIL PRODUCT,1.98,199512,16,22.154761905 ABCR,00075210,ABC RAIL PRODUCT,1.19,199809,15.11,15.11 ABCR,00075210,ABC-NACO INC,0.81,199903,13.5,19.5625 ABCR,00075210,ABC-NACO INC,1.71,199909,13.5,15.5 ABCR,00075210,ABC-NACO INC,0.01,200009,12.67,14.444 ABRX,00077R10,ABR INFO SVCS,0.18,199512,30.5,23.764805195 ABTC,00078210,ABT BUILDING PDT,1.44,199512,15.67,14.239545455 ACCC,00079410,ACC CORPORATION,0.01,199512,30,27.656538462 ACCI,00079H10,ACC CONSUMER FIN,0.44,199606,25,19.11 ACAH,00083310,ACA CAPITAL HLDG,69500,200712,10,15.6675 ACMR,00086T10,AC MOORE ARTS,0.45,199803,30,20.548504673 TACX,00088110,A CONSULT TEAM,4.3,199709,30,34 ADCT,00088610,ADC TELECOM,0.85,199512,24.71,27.656538462 ADCT,00088610,ADC TELECOM,2.33,199812,25.29,30.22 ADCT,00088610,ADC TELECOM,3,199912,25.14,36.782142857 ADCT,00088610,ADC TELECOM,4.86,200012,29.46,35.653333333 ;;;; run; data m_fegg; set m_fegg; yymm=input(put(yymm,6.),yymmn6.); format yymm yymmn6.; run; data sdc_final; set sdc_final; yymm_a=input(put(yymm_a,6.),yymmn6.); yymm_e=input(put(yymm_e,6.),yymmn6.); format yymm_a yymm_e yymmn6.; run; 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,'same')<=b.yymm<=a.yymm_a & a.acq_cusip=b.cusip; quit;
07-24-2017 05:37 AM
The formats control how values are displayed, not how they are calculated. The functions always work with the raw values.
07-24-2017 05:55 AM
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;
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.
07-24-2017 06:10 AM
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 .
07-24-2017 06:43 AM
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.
07-24-2017 06:45 AM
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.
07-24-2017 06:48 AM
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.
07-24-2017 06:59 AM
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.
If you want a variable populated with the first of the current month then the formula would need to look like:
07-24-2017 08:12 AM
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.;
07-24-2017 08:31 AM - edited 07-24-2017 08:33 AM
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.
07-24-2017 08:19 AM
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.
07-24-2017 08:27 AM
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:
Need further help from the community? Please ask a new question.