DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-25-2017 10:38 AM
Super User
Posts: 7,832

Re: should the date in the intnx functiointn be in date format?

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,832

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 97

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

Posted in reply to KurtBremser

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

Super User
Super User
Posts: 7,977

Re: should the date in the intnx functiointn be in date format?

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.

Frequent Contributor
Posts: 97

Re: should the date in the intnx functiointn be in date format?

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
Super User
Super User
Posts: 7,977

Re: should the date in the intnx functiointn be in date format?

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.

Super User
Posts: 5,516

Re: should the date in the intnx functiointn be in date format?

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.

Super User
Posts: 7,832

Re: should the date in the intnx functiointn be in date format?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,173

Re: should the date in the intnx functiointn be in date format?

@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');

 

Frequent Contributor
Posts: 97

Re: should the date in the intnx functiointn be in date format?

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
Frequent Contributor
Posts: 97

Re: should the date in the intnx functiointn be in date format?

Where is my result? I posted a picture.

Frequent Contributor
Posts: 97

Re: should the date in the intnx functiointn be in date format?

result

Super User
Super User
Posts: 7,074

Re: should the date in the intnx functiointn be in date format?

[ Edited ]

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.

Super User
Posts: 7,832

Re: should the date in the intnx functiointn be in date format?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,832

Re: should the date in the intnx functiointn be in date format?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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