Month-Year in if/where Statement

[Bright]

Hello,

I have a variable called month_year in dataset DATA1 with format MONYY7.

I am trying to make a subset of this datset based on the variable month_year. My code for getting the observations where month_year  is not March 2015 looks like this:

data DATA2 (where=(month_year ne 'MAR2015'd));
set  DATA1;
run;

I am getting the error "ERROR: Invalid date/time/datetime constant 'MAR2015'd.
ERROR: Syntax error while parsing WHERE clause."

Any suggestions?

Thanks!

[PeterClemmensen]

 What is your desired result? To get all obs where the date is in March 2015?

[Bright]

The desired output is to get all observations where the date is not March 2015.

[PeterClemmensen · Accepted solution]

Date values in SAS are integers. So March2015 is not a date in itself. 

I think this is what you want

data DATA2;
   set DATA1;
   where month_year > '31MAR2015'd or month_year < '01MAR2015'd;
run;

[Bright]

Thanks, but I only have month and year (i.e., MAR2015). I do not have the day (i.e., 31MAR2015).

[PaigeMiller]

---

@Bright wrote:
Thanks, but I only have month and year (i.e., MAR2015). I do not have the day (i.e., 31MAR2015).

---

Please provide more information. Is this variable character or numeric, according to PROC CONTENTS? Show us a typical value.

[Bright]

month_year in dataset DATA1 has format MONYY7.
For example MAR2015.

[PaigeMiller]

---

@Bright wrote:
month_year in dataset DATA1 has format MONYY7.
For example MAR2015.

---

So, because the format is MONYY7., it must be numeric. Obviously, you did not try the code from @PeterClemmensen which looks correct to me. Please try it.

Note: SAS dates always contain month and day and year, even if it is displayed differently.

[Bright]

I tried the code from @PeterClemmensen and it worked. Thanks both!

[PaigeMiller]

Adding to the correct comments from @PeterClemmensen , the only correct format to enter SAS day values is either the number of days since January 1, 1960 (which most people probably don't know), or as a data literal, which must be in this exact format

'19MAR2015'd

except that you can use lower case or mixed case letters, and (if you want, but really you shouldn't do this) you can use 2 digit years in some cases. So following this rule, your original code of 'MAR2015'd does not fit this format and so can't be used.

[Tom]

---

@PaigeMiller wrote:

Adding to the correct comments from @PeterClemmensen , the only correct format to enter SAS day values is either the number of days since January 1, 1960 (which most people probably don't know), or as a data literal, which must be in this exact format

'19MAR2015'd

except that you can use lower case or mixed case letters, and (if you want, but really you shouldn't do this) you can use 2 digit years in some cases. So following this rule, your original code of 'MAR2015'd does not fit this format and so can't be used.

---

The easier rule to remember is it needs to be something that the DATE informat will understand.

'01-mar-2015'd
"1 mar 15"d
'1MAR2015'd
etc.