Solved: Re: should the date in the intnx functiointn be in date format? - Page 3

Kurt_Bremser · Posted 07-25-2017 08:57 AM

Ok, here is my data. I wana pick the data from' event' as the date of 'benchmark'.

data WORK.BENCHMARK;
  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
;;;;
data WORK.EVENT;
  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 these two datasteps:

data benchmark;
set benchmark;
new_yymm_e = input(put(yymm_e,6.),yymmn6.);
format new_yymm_e yymmn6.;
run;

data event;
new_yymm = input(put(yymm,6.),yymmn6.);
format new_yymm yymmn6.;
run;

and you can use the new variables with the intnx() and intck() functions.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

lixuan · Posted 07-25-2017 09:10 AM

No, it don't work. even if I use new variable.

Tom · Posted 07-25-2017 09:29 AM

I really have no idea at this point why you are having trouble.

It seems very clear that source tables are stroing year and month into an integer value THAT IS NOT A DATE.

If you want to treat it as an actual date then CONVERT it to a date. The syntax for how to do this has been posted many times in the thread, but let's go over it gain.

You have a numeric variable where the ones and tens place represent the month in the year and the hundreds to hundred thousands place digits represent the calendar year. Here are a few ways you could convert that type of value to an actual date by assuming that you want to use the first day of the month.

date1 = input(put(199812,6.),yymmn6.);
date2 = input(put(199812,6.)||'01',yymmdd8.);
date3 = mdy(mod(199812,100),1,int(199812/100));

Once you have a date value you can attach a format to it to have print in many different ways. Personally I like the YYMMDD10. format since it avoids people confusing which part represents the month or having to be able to understand the English abbreviations of months. Also since it includes hyphens it should prevent you from trying to again interpret it as if it was just an integer like 19,981,201 instead of the human friendly representation of the date value.

Once you have your values as dates then you can use INTNX() and other date functions with the values.

Astounding · Posted 07-25-2017 08:51 AM

Did you check the values of YYMM in the permanent SAS data sets first? What did you find?

Depending on your answers, you may need a simple variation:

proc sql;
create table m_i_s1 as select b.*,a.yymm as yymm_a
from sdc.sdc_final as a,
ibes.m_fegg as b
where intnx('month',a.yymm, -36,'same')<=b.yymm<=a.yymm
& a.acq_cusip=b.cusip;
quit;

Now that you posted the sample data, I don't think this is the right program. I expect this will be solved by the time I can post an alternative but if not, I will post something.

Based on the sample data you posted below ... once you actually have cusips that match, you should be able to use my original idea without changing either of the data sets:

proc sql;

create table want as select b.*, a.yymm_a

from sdc_final1 as a, m_fegg1 as b

where (a.cusip = b.cusip) and (a.yymm_a - 300 <= b.yymm <= a.yymm_a);

quit;

The dates are six-digit numerics in YYYYMM form, so subtracting 300 gives you exactly 36 months earlier.

lixuan · Posted 07-25-2017 09:09 AM

Thanks ,but I don't have variable yymm in sdc_final.

lixuan · Posted 07-25-2017 09:12 AM

sdc_final is benchmark and m_fegg is event.

lixuan · Posted 07-25-2017 09:19 AM

Maybe here is better. name has been changed but data unchanged. thks

data WORK.SDC_FINAL1;
  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
;;;;
data WORK.M_FEGG1;
  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
;;;;

Kurt_Bremser · Posted 07-25-2017 09:25 AM

It does not make a difference.

Convert the yymm variables to SAS date variables and use those with the SAS date functions.

Post the code you ran on these example datasets.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

lixuan · Posted 07-25-2017 09:33 AM

HI, just like I posted above.

data m_fegg;
set ibes.m_fegg;
yymm=input(put(yymm,6.),yymmn6.);
format yymm yymmn6.;
run;
data sdc_final;
set sdc.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;

Kurt_Bremser · Posted 07-25-2017 09:47 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;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

lixuan · Posted 07-25-2017 10:34 AM

I get the reason, cusip are not the same. I should have substr cusip in m_fegg to 6 digit. I had done it before, but after I revised my code, I forgot it. It's not the matter of your codes. Thank you, thank you all. You make me learn a lot, I really appreciate.

Classroom Training Available!