BookmarkSubscribeRSS Feed
CharlesFowler72
Obsidian | Level 7

Hi guys,

 

hopefully a simple 1 for someone.

 

I'm trying to use a where function with a date and keep coming up with an error. With my usual date format I always use...

 

Where Date ="DDMMMYYYY"d;

 

and that works fine but for the date format YYMMN6. it doesn't seem to work. I know that I can convert to the number and it will work fine so...

 

Where Date = 21001;  is equivalent to Where Date = 201707;

 

but i was hoping there is an equivalent to the quotation marks for this date format. Any help would be great.

 

Regards,

 

CharlesFowler72

 

4 REPLIES 4
ballardw
Super User

The only form of date you can use with the date litteral construct is the DATE7 or Date9 appearance: "ddMONyy"d or "ddMONyyyy"d where MON is the three letter abreviation such as "10Jan2017"d

 

Note that there are so many digit only date formats this is a reasonable rule. For instance can you tell which date this is: 2017251 quickly? It is a common date format in some organizations. Or how about 010203?

Reeza
Super User

@CharlesFowler72 wrote:

 

 

I'm trying to use a where function with a date and keep coming up with an error. With my usual date format I always use...

 

Where Date ="DDMMMYYYY"d;

 

and that works fine but for the date format YYMMN6. it doesn't seem to work.

 

This is the correct approach, assuming you mean ddMONyyyy or the date9 format, "01Jan2017"d. If it's not working, include your code and log. If you want the full month you can also use functions on the dates.

 

Where year(date)=2017 and month(date)=7;

 

I suggest you post your code and log and verify the format and type of your variable.

Naveen45
Fluorite | Level 6

I just dint get your question.. can you just give  the data and let me know what you want to filter, i will try to help you @CharlesFowler72

Tom
Super User Tom
Super User

The format you have attached to a variable just impacts how the value is displayed, not what values are stored in the variable.

So if your date varaible is actually a date value with the YYMMN6. format attached to it your WHERE statement should continue to use normal date literals.   Also because your date format is not displaying the day of the month your actual value could be any of the days of that month.  If you know that your data always is set to the first of the month then you could use = in the WHERE clause.

where date='01JUL2017'd

Otherwise you need to test for the full range of dates.

where '01JUL2017'd <= date < '01AUG2017'd 

or

where intnx('month',date,0,'b') = '01JUL2017'd 

or

where put(date,yymmn6.) = '201707'

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 5611 views
  • 1 like
  • 5 in conversation