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

[lixuan]

PLS let me study for a while. I have many question about your codes. If I have any question, It' will be very grateful that you can teach me .

[lixuan]

Hi,   why can't I just input(yymm, yymmdd8.)?  and what the meaning of !! and '01' in input function? Thanks

[Tom]

---

@lixuan wrote:

Hi,   why can't I just input(yymm, yymmdd8.)?  and what the meaning of !! and '01' in input function? Thanks

---

Let's look at the syntax you proposed.

input(yymm, yymmdd8.)

So the INPUT() function is expecting a character string in the first argument. But isn't your variable a number (really an integer)? If so then in a data step SAS would silently convert that number to a string using the BEST12 format. So a value like 199,812 would become '      199812'.  You then tell the INPUT() function to read then first 8 characters as if it was a date in YMD order.  So that would try to read six spaces followed by '19'.  That is not really a valid character representation of any valid date. And if you were using it in SQL statement then SAS would complain that YYMM variable was not a character string.

On top of that even if YYMM was a character string and had a value like '198812' in it if you tried to read that using the YYMMDD8. informat it would fail.  The informat can handle strings with only 6 digits, but then it just assumes that you are using two digit years. In which case '88' is not a valid month.

So if you want to use the YYMMDD informat you really need to supply values for the YY the MM and the DD.  So that is why the concatenation operator, || (which can also be represented by !! to support character sets and keyboards that do include the | character), and the extra '01' is used to supply the day of the month.

But you could also just use the YYMM informat instead, but that informat is new (less than 20 years old 🙂 ) so many users don't think to use it.

[lixuan]

 Hi tom, you explain it very clearly. But I am puzzled with ||. just because of put(yymm,6.), did you ad！！? If I want ouput yymm, can I change the code like this? But it didn't work. Sorry ,because I am a new user. Also, where can I find the date format like yymmn6 etc.? Thank you 

date_correct = input(put(yymm,6.) '01',yymm6.);
format date_correct date6.;

[Tom]

---

@lixuan wrote:

 Hi tom, you explain it very clearly. But I am puzzled with ||. just because of put(yymm,6.), did you ad！！? If I want ouput yymm, can I change the code like this? But it didn't work. Sorry ,because I am a new user. Also, where can I find the date format like yymmn6 etc.? Thank you 

date_correct = input(put(yymm,6.) '01',yymm6.);
format date_correct date6.;

---

Didn't you get any error message from SAS when you tried that?

What did they tell you?  You really need to learn how to read the error messages since it will help you correct your mistakes and typos.

Let's run it and see.

151  data _null_;
152   yymm=198812;
153  date_correct = input(put(yymm,6.) '01',yymm6.);
                                       ---- ------
                                       22   48
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =,
              >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

ERROR 48-59: The informat YYMM was not found or could not be loaded.

154  format date_correct date6.;
155  run;

So the first error message shows that you didn't include the concatenation operator between the two strings. It is listing all of the possible operators that you might want to place between two value.

The second error shows that YYMM is NOT a valid informat.  Look at the list of possible informats.  A quick google search like this should find a number of SAS man pages. https://www.google.com/search?q=%40sas.com+date+informat

So if we look at the page for SAS 9.4 formats by category we see these two informats.

YYMMDDw. Informat	Reads date values in the form yymmdd or yyyymmdd.
YYMMNw. Informat	Reads date values in the form yyyymm or yymm.

So it looks we need to add the letter N after YYMM to get the right informat name.

[lixuan]

Yes , I tried the right informat first, but I added a comma before !!, so it showed diffterent information. Thank you very much for your patience. 

date_correct = input(put(yymm,6.) !! '01',yymmn6.);
format date_correct yymmn6.;

[Tom]

---

@lixuan wrote:

Yes , I tried the right informat first, but I added a comma before !!, so it showed diffterent information. Thank you very much for your patience. 

date_correct = input(put(yymm,6.) !! '01',yymmn6.);
format date_correct yymmn6.;

---

I still think you are confused about what you are telling SAS to do. In this example it works since you have eliminated the syntax error, but you have made the step more complicated than it needs to be.

First you converted the number into a 6 digit string. Then you appended two more digits. Then you used only the original 6 digits.  So why did you append the '01' if you were not going to use it?

It would be less confusing if you would either add the 01 and use the YYMMDD informat or don't add the 01 and use the YYMMN informat.

date_correct1 = input(put(yymm,6.) !! '01',yymmdd8.);
date_correct2 = input(put(yymm,6.),yymmn6.);
format date_correct1 date_correct2 yymmn6.;

[Kurt_Bremser]

Setting a specific day for the date input came from me (in another thread of @lixuan). I did that to show that a) a SAS date value will always denote a specific day and b) you want to avoid letting the computer guess what you want to do.

I hope that he/she now starts to grok what a date value is and what roles formats play.

[Astounding]

I think I finally figured out what is happening here.  Here's my theory, in two parts.

Part 1:  Your variables already contain legitimate SAS dates

This means that there is no need to add "01" to the end of anything.  You were misled by the yymmn6 format, which displays only the month and the year.  But the variables are storing legitimate SAS dates that references the day as well.  You can check this part of the theory by running this program:

proc print data=have (obs=25);

var yymm;

format yymm;

run;

The FORMAT statement removes any format so PROC PRINT shows the actual value stored in YYMM.  If I'm right, PROC PRINT will show many different values in the last two digits of YYMM.  If I'm wrong, PROC PRINT will show only 01 to 12 as the last two digits, and you can disregard my theory.

Part 2:  The INTNX formula is wrong

INTNX was being applied in this formula:

intnx('month',a.yymm,-36)<=b.yymm<=a.yymm

However, that doesn't do what you expect.  It does go back 36 months, but returns the FIRST day of the month from 36 months ago.  That's where the extra days (roughly 15 days on average) are creeping in to your time period.  To get the same day of the month from 36 months ago, the formula should be:

intnx('month',a.yymm,-36, 'same')<=b.yymm<=a.yymm

[lixuan]

Right, I think I do understand what you said , and I revise my code, but the result is no observation. I check my code and find no mistake. 

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;

[lixuan]

I had used Astounding's code directly, and I could get  about 2300 observations.

(a.yymm - 300) <= b.yymm <= a.yymm

[Kurt_Bremser]

It is now time that you post your input data. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset (just 10 observations will probably suffice) to a data step and post that here.

The macro will preserve all values and formats as they are, and will most probably give us all clues we need to solve your problem.

[lixuan]

Sorry , but I have problems. first, where is myfolds or any fold that can find the downloaded code? second, I can't find anywhere I can edit autoexec file. I use sas9.4. 

[Kurt_Bremser]

The original description for the macro assumes you use SAS UE.

If you have a different setup, just store the zip file on your computer and extract the .sas file from it.

You can then either open it with EG, or open it with the Windows Editor and then copy/paste the code to SAS Studio.

Run it once so that the macro is compiled, and then use it as documented.

[lixuan]

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
;;;;