BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lixuan
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

40 REPLIES 40
lixuan
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Super User

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
Opal | Level 21

@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
Obsidian | Level 7

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; result
lixuan
Obsidian | Level 7

Where is my result? I posted a picture.

Tom
Super User Tom
Super User

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
Super User

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
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 40 replies
  • 3529 views
  • 3 likes
  • 6 in conversation