How to subset by a date that is in YYQ format.

[kz_]

I would like to filter a data set by a date column that is in YYQ format. 

The data look something like this: 

group  pay_quarter N 

1          2019Q1      454

1          2020Q1      500 

2          2021Q1      488

3          2021Q2      400

When I click on the 'pay_quarter' column, SAS specifies that it is type numeric, format YYQ. 

Can someone explain to me why this doesn't work: 

proc sql; 

     create table want as 

           select * 

           from have 

           where pay_quarter in ('2019Q1'd, '2021Q1'd); 

quit; 

I have found that I can do this 

where pay_quarter in (21550, 22281); 

but I'm hoping there is a more user-friendly way. 

[Reeza]

where put(pay_quarter, YYQ.) in ('2019Q1', '2021Q1');

Formats only change the appearance of the data not the underlying values, so when you filter or do calculations using WHERE it requires the unformatted values. Some procs will aggregate on formatted values or there are options to have it recognized in certain conditions but it's more a matter of understanding the usage.

[PaigeMiller]

where pay_quarter in ('2019Q1'd, '2021Q1'd); 

This comparison of a variable to specific values fails for two reasons.

The comparison is always done with UNformatted values, and so even though the value of pay_quarter may appear to you as 2019Q1, the underlying date may in fact be '15JAN2019'd or '16JAN2019'd or any other date in the first quarter of 2019. Second reason it fails is that '2019Q1'd is a meaningless construct in SAS and should produce an error. So, a possible comparison might be

where pay_quarter in ('01JAN2019'd,'02JAN2019'd,'03JAN2019'd, ... );

which is invalid code because of the triple dots, but you get the idea, no one wants to type out all of the days in the first quarter, that would be 91 days ... you get the point.

Here is valid code:

where '01JAN2019'd<=pay_quarter<='31MAR2019'd or '01JAN2021'd<=pay_quarter<='31MAR2021'd;

Alternatively, you can use the INTNX function

where intnx('month',pay_quarter,0,'b') in ('01JAN2019'd,'01JAN2021'd);

which converts any day to the very first day of the quarter and then you can do the comparison to '01JAN2019'd (that's the first day of the quarter of interest) or to '01JAN2021'd (also the first day of the quarter of interest).

Alternatively, @Reeza shows how to do this with the PUT function.

If you are going to work with dates that are formatted, the RED above is a key piece of information that you must understand and use.

[ballardw]

If you give some more rules of what you actually want there may be more friendly ways.

For example, if you only want the first quarter for two specific years:

where quarter(pay_quarter)=1 and year(pay_quarter) in (2019,2021)

may be what you want.

If you  wanted just the first quarter regardless of year then

where quarter(pay_quarter)=1 

Date values let you use a number of functions for extracting or manipulating the dates but knowing what is desired helps.