BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Bright
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

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

Bright
Obsidian | Level 7
The desired output is to get all observations where the date is not March 2015.
PeterClemmensen
Tourmaline | Level 20

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
Obsidian | Level 7
Thanks, but I only have month and year (i.e., MAR2015). I do not have the day (i.e., 31MAR2015).
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Bright
Obsidian | Level 7
month_year in dataset DATA1 has format MONYY7.
For example MAR2015.
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Bright
Obsidian | Level 7
I tried the code from @PeterClemmensen and it worked. Thanks both!
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1005 views
  • 0 likes
  • 4 in conversation