Date formats

[sas940]

I know this should be easy but I have not done SAS in over 10 years and I am trying to get back into it.

I have two date fields

NAME             TYPE                  LEN                  FORMAT                INFORMAT

MONTH            NUM                    8                       11.1                          11.1

left_date            NUM                    8                       DATE9.                    DATE9.

MONTH looks like:    202010

left_date looks like: 15DEC2020

All I want to do is create a dataset where I get those values for each variable with the same MONTH and YEAR..  I have tried reformatting left_date using format new_Co_Date YYMMN.; but to no avail. 

I would want to do, last step, where month_end= left_date (or with applicable new variable name(s)).

Any help is greatly appreciated.

Thank you

[PaigeMiller]

MONTH is not a SAS date value. A SAS date value is the number of days since 01JAN1960. MONTH is a simple integer, that some humans interpret as a year and month, but SAS does not know it might be a year/month. You cannot work with MONTH as a SAS date value unless you convert it to a SAS date value, and make it the number of days since 01JAN1960. This is how you do this:

month1 = input(put(month,8.),yymmn6.);

Now, MONTH1 is a SAS date value.

LEFT_DATE is a SAS date value, since when you apply the format DATE9., it produces a date that is readable and makes sense in the proper context to humans.

So now, you can compare MONTH1 and LEFT_DATE, as SAS always uses the number of days since 01JAN1960 for logical or arithmetic operations, regardless of format. So, now it make sense to write things such as

if month1 > left_date then ...;

or

delta = left_date - month1;

You said you want: "All I want to do is create a dataset where I get those values for each variable with the same MONTH and YEAR". This is not specific enough for me to understand, and it would certainly help if you gave small examples of your data set(s).

[sas940]

So, now I have

month that reads as 202010

and I have left_date that reads as 22OCT2020

I would like to be able to "read" left_date as 202010 the sasme as MONTH reads.  

Then in y final dataset I can match the records that have the same month end, 202010, and left_date.

[PaigeMiller]

---

@sas940 wrote:

So, now I have

month that reads as 202010

and I have left_date that reads as 22OCT2020

I would like to be able to "read" left_date as 202010 the sasme as MONTH reads.  

Then in y final dataset I can match the records that have the same month end, 202010, and left_date.

---

No, I don't think that's what you want. For SAS to make any sense of these date values, they both have to be valid SAS date values, in other words, the number of days since 01JAN1960. I showed you how to turn MONTH into MONTH1 which is a valid SAS date value. It will always be the first day of the month, so it still won't match 22OCT2020 which is not the first day of the month. So you have to turn that into the first day of the month as well

left_date1=intnx('month',left_date,0,'b');

Now, LEFT_DATE1 and MONTH1 will be matchable, something like this

if left_date1=month1 then ... ;

The main point here is that SAS will not compare the formatted values, or unformatted value to formatted value. SAS compares unformatted values, which is why I keep talking about the need to turn your dates into the number of days since 01JAN1960, then all the comparisons work.

[ballardw]

Actually your shown value for Month does not actually meat the Format assigned. With an 11.1 format it will by default always display with one decimal.

Plus the Informat being 11.1 makes one suspect that you have some values that are not as you portray in general. Why does this have an 11.1 informat and format?

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[sas940]

THank you.  I cannot comment on the "why" between the 11.1 as that is simply what I see.  My apologies 

[ballardw]

---

@sas940 wrote:

THank you.  I cannot comment on the "why" between the 11.1 as that is simply what I see.  My apologies 

---

Sometimes it is worth investigating such oddities as it could indicate that the actual "original" value was something else and the values you see now have been modified, and maybe not in your best interest. An 11.1 informat could have truncated values or moved a least significant digit to a decimal value which was later removed.

You might want to look at the result of this example code for the values of X. The last could well have been a date in YYMMDD format but gets mangled by an unusual informat.

data example;
   input x 11.1;
datalines;
1
12
312
0312
20312
200312
0200312
20200312
;

If you don't need to add one or more decimal positions to a value when read because the source data does not have any decimal but the value should have some then an Informat with a decimal is inappropriate.

[Tom]

So you have one variable with a date and another variable with a simple number.

You can either convert the number into a date and compare them. To ignore the day of the month use the INTNX() function.

if intnx('month',left_date,0) = intnx('month',input(put(month,Z6.),yymmn6.),0) then ....

Or convert them both into character strings and compare them.

if put(left_date,yymmn6.) = put(month,z6.) then ....

You should find out how that existing dataset was created and probably fix that step. It seems silly to have the format of 11.1 attached to that variable if the values are just the digits YYY,YMM. There is no value to display after the decimal point.  And it is damaging to have the informat of 11.1 attached to the variable.  If you tried to read the string '202006' with an informat of 11.1 then you would get 20,200.6 instead of 202,006 because the .1 on the informat means to assume the last digit is after the decimal point when the string being read does not include a period.