topic Re: Month-Year in if/where Statement in SAS Programming

Month-Year in if/where Statement

Bright — Mon, 19 Sep 2022 13:36:23 GMT

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!

Re: Month-Year in if/where Statement

PeterClemmensen — Mon, 19 Sep 2022 13:32:40 GMT

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

Re: Month-Year in if/where Statement

Bright — Mon, 19 Sep 2022 13:34:20 GMT

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

Re: Month-Year in if/where Statement

PeterClemmensen — Mon, 19 Sep 2022 13:35:39 GMT

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;

Re: Month-Year in if/where Statement

Bright — Mon, 19 Sep 2022 13:38:26 GMT

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

Re: Month-Year in if/where Statement

PaigeMiller — Mon, 19 Sep 2022 13:39:53 GMT

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.

Re: Month-Year in if/where Statement

PaigeMiller — Mon, 19 Sep 2022 13:43:33 GMT

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

Re: Month-Year in if/where Statement

Bright — Mon, 19 Sep 2022 13:43:22 GMT

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

Re: Month-Year in if/where Statement

PaigeMiller — Mon, 19 Sep 2022 13:45:23 GMT

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

Re: Month-Year in if/where Statement

Bright — Mon, 19 Sep 2022 13:52:04 GMT

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

Re: Month-Year in if/where Statement

Tom — Mon, 19 Sep 2022 16:11:33 GMT

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