DATA Step, Macro, Functions and more

Where funtion with date format YYMMN6.

Reply
New User
Posts: 1

Where funtion with date format YYMMN6.

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

 

Super User
Posts: 11,343

Re: Where funtion with date format YYMMN6.

Posted in reply to CharlesFowler72

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?

Super User
Posts: 19,822

Re: Where funtion with date format YYMMN6.

Posted in reply to CharlesFowler72

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.

Occasional Contributor
Posts: 9

Re: Where funtion with date format YYMMN6.

Posted in reply to CharlesFowler72

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

Super User
Super User
Posts: 7,060

Re: Where funtion with date format YYMMN6.

[ Edited ]
Posted in reply to CharlesFowler72

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'

 

Ask a Question
Discussion stats
  • 4 replies
  • 138 views
  • 1 like
  • 5 in conversation