topic Re: should the date in the intnx functiointn be in date format? in SAS Programming

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

lixuan — Mon, 24 Jul 2017 09:35:24 GMT

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.

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

Kurt_Bremser — Mon, 24 Jul 2017 09:37:50 GMT

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

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

lixuan — Mon, 24 Jul 2017 09:41:23 GMT

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

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

RW9 — Mon, 24 Jul 2017 09:55:20 GMT

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.

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

lixuan — Mon, 24 Jul 2017 10:10:18 GMT

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

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

RW9 — Mon, 24 Jul 2017 10:43:13 GMT

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.

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

Astounding — Mon, 24 Jul 2017 10:45:49 GMT

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.

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

Kurt_Bremser — Mon, 24 Jul 2017 10:48:28 GMT

The intnx function (read the documentation here: http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p10v3sa3i4kfxfn1sovhi5xzxh8n.htm) 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.

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

Patrick — Mon, 24 Jul 2017 10:59:16 GMT

@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#p1wj0wt2ebe2a0n1lv4lem9hdc0v.htm

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

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

lixuan — Mon, 24 Jul 2017 12:12:46 GMT

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;

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

lixuan — Mon, 24 Jul 2017 12:14:07 GMT

Where is my result? I posted a picture.

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

lixuan — Mon, 24 Jul 2017 12:14:57 GMT

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

Kurt_Bremser — Mon, 24 Jul 2017 12:19:31 GMT

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.

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

Kurt_Bremser — Mon, 24 Jul 2017 12:27:41 GMT

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

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

Tom — Mon, 24 Jul 2017 12:33:49 GMT

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.

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

lixuan — Mon, 24 Jul 2017 12:40:27 GMT

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 .

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

lixuan — Mon, 24 Jul 2017 15:50:06 GMT

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

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

Tom — Mon, 24 Jul 2017 16:19:29 GMT

@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.

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

lixuan — Mon, 24 Jul 2017 16:59:22 GMT

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

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

Tom — Mon, 24 Jul 2017 17:14:03 GMT

@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.