BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kurt_Bremser
Super User

@lixuan wrote:

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.

 

lixuan
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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

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

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

lixuan
Obsidian | Level 7

sdc_final is benchmark and m_fegg is event.

lixuan
Obsidian | Level 7

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

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.

lixuan
Obsidian | Level 7

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

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.

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
  • 3584 views
  • 3 likes
  • 6 in conversation