topic Re: how to filter character variable month to create a new dataset in SAS Programming

how to filter character variable month to create a new dataset

ForrestYao — Mon, 13 Jan 2020 16:04:39 GMT

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!

Re: how to filter character variable month to create a new dataset

novinosrin — Mon, 13 Jan 2020 16:13:39 GMT

Hi @ForrestYao Something like the below where expression may work

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

Re: how to filter character variable month to create a new dataset

Krueger — Mon, 13 Jan 2020 16:16:49 GMT

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.

Re: how to filter character variable month to create a new dataset

ForrestYao — Mon, 13 Jan 2020 16:27:07 GMT

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

Re: how to filter character variable month to create a new dataset

novinosrin — Mon, 13 Jan 2020 16:33:20 GMT

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?