topic Re: Questions Regarding Using a Where Statement with Dates and the "D" Statement in SAS Programming

Questions Regarding Using a Where Statement with Dates and the "D" Statement

JackZ295 — Thu, 14 Jul 2022 20:11:04 GMT

Hi! I have a data set where my dates are formatted as follows:

YYYY-MM-DD

Here is the code that I used to convert my date data to the format above:

data one_quality;
set one;
started_time_new=compress(substr(started_time,1,10));
received_on_new=compress(substr(received_on,1,10));
run;

data one_quality_new; 
set one_quality; 
started_time_num=input(started_time_new, anydtdte10.); 
received_on_num=input(received_on_new, anydtdte10.);
survey_date_num=input(survey_date,anydtdte10.); 
format started_time_num received_on_num survey_date_num YYMMDD10.; 
run; 

proc print data=one_quality_new; 
var started_time_num started_time_new received_on_new received_on_num 
survey_date survey_date_num; 
run;

Here is a sample of my print output:

I want to filter my print results such that only dates (survey_date_num) after 2022-05-12 are printed. I wrote the below code in an attempt to achieve those results:

title 'Print Out of One_Quality_New Data Set After 5/12/2022'; 
proc print data=one_quality_new; 
where survey_date_num >= "2022-05-12"d;
var survey_date_num household_name; 
run;

However, when I tried to run this code, I ran into the following errors:

ERROR: Invalid date/time/datetime constant "2022-05-12"d.

ERROR: Syntax error while parsing WHERE clause.

Is there a reason why I am running into this error? Any input regarding this would be much appreciated! Thanks!

Re: Questions Regarding Using a Where Statement with Dates and the "D" Statement

ballardw — Thu, 14 Jul 2022 20:18:08 GMT

The date literals must be the DATE format appearance: "01JAN2022"d , or for those that live dangerously you can use 2-digit years. It doesn't matter what format your variable is the comparison uses literal values in the Date format with the D indicator.

It simplifies code for development and helps understanding when reading someone else's code which date is meant. Wait til you try to decipher what date someone means when given "010203" and told that is a date.

Re: Questions Regarding Using a Where Statement with Dates and the "D" Statement

Tom — Fri, 15 Jul 2022 00:36:28 GMT

If you want to use a date constant the text inside the quotes has to be something the DATE informat can read.

where survey_date_num >= "12MAY2022"d;
where survey_date_num >= "12May2022"d;
where survey_date_num >= "12may2022"d;
where survey_date_num >= "12 MAY 2022"d;
where survey_date_num >= "12-MAY-2022"d;

The format attached to the variable in the dataset being read does not matter since a format is just instructions for how to display the value as a string. So what format you have attached to a variable has no impact on what value is actually stored in the variable. You could also just use the actual number of days, which for that date is the number 22777.

where survey_date_num >= 22777;

Example:

1579  data check;
1580    date1= input('2022-05-12',yymmdd10.);
1581    date2 = "12MAY2022"d ;
1582    put date1= / date2= ;
1583  run;

date1=22777
date2=22777