how can we filter on yyyymm in proc sql ?

Question

how can we filter on yyyymm in proc sql ?

Ken (IS/IT--Management)

(OP)

28 Sep 16 02:01

Hi,

Wanted to filter on variable YrMth, that store value as yyyymm (datattype as number).

On using proc sql with condition as
WHERE YrMth = 201505, the result returns 0 rows.

How can we use filter that has value stored as yyyymm and return rows instead 0 row ?

Thanks,

DaiChi

klaz2002 (Programmer) · Answer 1 · 2017-02-02 15:18:25

I think that you need to 'see' the actual value stored to get a sense on how to filter. SAS has two datatypes char and num. In your question you specify that the variable is type NUM. This indicates that the number stored is in numeric format. For you to 'see' this as a date value on display means that SAS has converted what it has stored for your eyes only. SAS stores date values as the number of days from Jan 1, 1960. This means that if you want to filter for records in the month of May 2015 you need to use values in the range of 20209-20239. For obvious reasons this is way too complicated and most of us do not do this.

One way to do this in SAS code is as follows:

CODE

WHERE '30APR2015'd < YrMth <= '31MAY2015'd;

or

CODE

WHERE put(YrMth, yymmn6.) = '201505';

Notice the quotes around the filter text.

I hope this has helped you.
Klaz

I hope that this helps.

Klaz

Come Join Us!

Posting Guidelines

Jobs

how can we filter on yyyymm in proc sql ?