how to filter character variable month to create a new dataset

[ForrestYao]

Hi there,

I have a dataset with multiple variables for fiscal year 2019. One column called Year is a numeric variable eg 2018, 2019 etc. Another column called month is a character variable eg Oct, Nov, Dec etc. I need to generate a new dataset called fiscal year 2020 which contains everything from 2018 November to 2019 October. My patial code is:

proc sql;
create table perm._fydata_2020 as
select * from perm._fydata_2019
where month...(should start from 2018 Nov to 2019 Oct)

Because month is a character variable, I am wondering how do I filter a character variable...

Thanks in advance!

[novinosrin]

Hi @ForrestYao  Something like the below where expression may work

where '01nov2018'd<=input(cats('01',month,year),date9.)<='31oct2019'd

[ForrestYao]

@novinosrin Hi sir, could you explain what does "01" represent inside of CATS function?

[novinosrin]

First off, to Sir who is that? I am a SAS Janitor. 

Okay, basically, I am concatenating a constant day value with '01' as the 1st day of the month. Since your range that you mentioned 2018 NOV-OCT2019. It's safe to assume the whole months starting from 01NOV2019 to 31OCT2019. Assuming you do not have a DATE or DAY variable with values of day of the month, the best bet is to have the 1st day of the month making it inclusive in the specified range.  

Of course, you do have the other 2. Therefore the concatenated string would look like 01NOV2018 etc. Then you convert the string to a numeric value using the INFORMAT date9. . I suppose everything else is straight forward plugging in the range within the WHERE clause. Hope this helps!

---

@ForrestYao wrote:

@novinosrin Hi sir, could you explain what does "01" represent inside of CATS function?

---

[Krueger · Accepted solution]

One way is listing them out individually...

WHERE Month in ('string1', 'string2', 'string3')

What you might be able to try to do is setup a sort that has it sorted in the correct order and then rank them. At that point you could filter based off the rank possibly. If you have an example dataset it would be easier to help expand on potential solutions.