topic Re: Understanding of today() between in SAS Programming

Understanding of today() between

David_Billa — Thu, 23 Jun 2022 10:26:37 GMT

Can someone help me understand the meaning of this SAS code?

today() between Start_dt and End_dt

Re: Understanding of today() between

Patrick — Thu, 23 Jun 2022 10:53:12 GMT

The today() function returns a SAS Date value representing the current date (numerical value; count of days since 1/1/1960)

SAS variables start_dt and end_dt must also contain SAS Date values

This expression returns TRUE if the value for today() is >= start_dt and <= end_dt

Another way to write the expression would be: start_dt <= today() <= end_dt

Hope that explains it to you.

If you don't understand how SAS Date, DateTime and Time values work then I'd strongly recommend some "Googling" to find the relevant SAS documentation. It's important that you do understand this and it's explained in detail in the SAS docu.

Re: Understanding of today() between

PaigeMiller — Thu, 23 Jun 2022 11:26:11 GMT

Adding to @Patrick 's explanation

BETWEEN can only be used in PROC SQL, not elsewhere in SAS.

Re: Understanding of today() between

Patrick — Thu, 23 Jun 2022 11:51:37 GMT

@PaigeMiller

Let's say where clause given below syntax is also valid in SAS data steps.

data have;
  start_dt=today()-10;
  end_dt=today()+10;
run;

data demo_1;
  set have(where=(today() between start_dt and end_dt));
run;

data demo_2;
  set have;
  where today() between start_dt and end_dt;
run;

proc sql;
  create table demo_3 as  
  select *
  from have
  where today() between start_dt and end_dt
  ;
quit;

Re: Understanding of today() between

PaigeMiller — Thu, 23 Jun 2022 11:53:18 GMT

Thanks, @Patrick

I have never used BETWEEN in that way. So then it would be correct to say BETWEEN is valid in WHERE statements/clauses?

The following doesn't work

data a;
    set sashelp.class;
    if age between 14 and 15 then age_14_15_flag=1;
    else age_14_15_flag=0;
run;

Re: Understanding of today() between

garkbeda43 — Thu, 23 Jun 2022 13:43:00 GMT

Thanks for solution..

Re: Understanding of today() between

Tom — Fri, 24 Jun 2022 02:55:44 GMT

Basically WHERE clauses mimic SQL functionality. So you use use BETWEEN and LIKE.

If suspect it is so all of the code that SAS uses to do implicit pass thru to remote SQL databases can be reused in processing WHERE clauses.

Note that some things that SQL does not support the WHERE clause does. Like colon modifier on comparison operators.

1520  proc print data=sashelp.class;
1521    where name =: 'A' ;
1522  run;

NOTE: There were 2 observations read from the data set SASHELP.CLASS.
      WHERE name=:'A';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1523
1524  proc sql;
1525    select * from sashelp.class
1526    where name =: 'A'
                    -
                    22
                    200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
              a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE, INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE,
              USER.

ERROR 200-322: The symbol is not recognized and will be ignored.

1527    ;
1528  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds