how can we filter on yyyymm in proc sql ?

Ken · Sep 27, 2016

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 · Feb 2, 2017

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

how can we filter on yyyymm in proc sql ?

Ken

IS-IT--Management

klaz2002

Programmer

Similar threads

Part and Inventory Search

Sponsor