topic Re: filtering by date using where statement in SAS Programming

filtering by date using where statement

chelseah — Fri, 08 Nov 2019 19:18:21 GMT

I've searched the sas community and have tried changing the format of my where statement and can't figure out why sas won't recognize the rows with the date '201801'. I'm a beginner coder and suspect its my lack of fully understanding date formats that is creating the problem.

So I have a date variable 'dob' which is formatted mm/dd/yyyy. I create a new variable 'birthmo' from the dob variable to create a date with the format yyyymm. The variable is created correctly (or so I think) and when I do a frequency on birthmo it shows 44k occurences of 201801. But when I try and filter the data using 'where birthmo = '201801'd;' I get this error:

ERROR: Invalid date/time/datetime constant '201801'd.
ERROR: Syntax error while parsing WHERE clause.

Or if I change the code to read 'where birthmo = 201801;' it comes up with an empty dataset.

CODE:

data cohort ;
set m.mpcf_201801 m.mpcf_201802 m.mpcf_201803;
birthmo = dob;
FORMAT birthmo yymmn6.;
run;

data cohort2;
set cohort;
where birthmo = '201801'd;
run;

Sample DATA:

individual id	dob
1345	10/20/2015
315345	6/5/2018
465	9/28/2010
11354	1/1/2018
1341	1/23/2018
352	1/24/2018
4652	2/8/2013
435	5/14/2009
122	1/18/2018
1253	6/19/2004
463	7/10/2016
78986	1/22/2018
25123	1/2/2012

Thank you for your help!

Re: filtering by date using where statement

novinosrin — Fri, 08 Nov 2019 19:24:20 GMT

Correcting your Where statement(methinks the following should do)

where put(birthmo,yymmn6. -l) = '201801';

Re: filtering by date using where statement

ballardw — Fri, 08 Nov 2019 19:29:07 GMT

Literal values must be in DATE9 or DATE7 appearance:

'01JAN2019'D or '01JAN19'd , a syntax rule because there are so many random collections of digits masquerading as dates. Did you that today , 8 Nov 2019 could be 2019312? or even worse 19312?

AND they must be a complete date, day month and year.

Or explicitly use the formatted value

Where put(birthmo,yymmn6.) = '201810';

Re: filtering by date using where statement

mkeintz — Fri, 08 Nov 2019 19:38:52 GMT

Using the

'xxxxxxx'd or "xxxxxxx"d

is the SAS convention for specifying a date literal (like 5 is a numeric literal and "abc" is a character literal).

But there is only one format allowed in a date literal. In your case you would want

where '01jan2010'd <= dob <='31jan2010'd;

The important thing to understand is that is doesn't matter at all what the format of DOB is - the underlying numeric value (number of days after 01jan1060) is the same - it's just displayed differently according to the assigned format of DOB. So specifying the date-literals in the where statement as I have merely inserts the number of days after jan 1, 1960 that represent the dates 01jan2010 and 31jan2010.

You could also have

where year(dob)=2018 and month(dob)=1;

which may be much more intuitive, but requires executing two functions for every observation. Using the date literal bounds requires no such overhead - the actual numeric values are inserted once and for all when your program is being compiled, prior to execution.

Re: filtering by date using where statement

chelseah — Fri, 08 Nov 2019 19:32:21 GMT

It's like magic! No need to create the birthmo variable either! Thank you!

Re: filtering by date using where statement

novinosrin — Fri, 08 Nov 2019 19:36:12 GMT

@chelseah That's right. While I am one extremely lazy person, please do pay attention to @mkeintz 's explanation of SAS dates that are merely numbers formatted for display while internal stored value is in tact. 🙂

Re: filtering by date using where statement

chelseah — Fri, 08 Nov 2019 19:40:20 GMT

Yes, I have taken trainings regarding dates and I realize how they are stored, but for some reason I can't seem to apply that knowledge to writing code. Once I think I understand, I come across a problem, like today. I'll keep trying to consume the literature and hopefully it will click one day.