SAS Proc SQL function in where clause for only current week

Question

I have some data in week-date-time format ie 14dec2020 00:00:00:0000. I am using SAS and a proc sql query

This data contains many weeks worth of data but i was curious if theres in way to only pull data relevant to only current week? IE today is 17dec2020 so i would want to only pull data for the week of 14dec2020. if today was 22dec2020 then i would want it to pull data for the week of 21dec2020.

Here is one of the many queries i have tried.

data have;
  today = today();    
  wkday = weekday(today); 

  start = today - (wkday - 1);  
  end = today + (7 - wkday); 

  length cstart cend $30;
  cstart = put(start, date9.) || ' 00:00:00.0000' ;
  cend = put(end, date9.) || ' 00:00:00.0000' ;

  call symput('start', cstart);
  call symput('end', cend);
run; 


Proc Sql;
  connect to odbc (environment=x user=y p=z);
  create table basic.curweek as select * from connection to odbc
       (select year, month, week, store, sales, SKU
            from datatable
            where (&start. <= week <= &end.)
            order by sku);
 disconnect from odbc;
 quit;

Thanks to the help of the great people below i have gotten to this state. But am still facing some syntax errors. Any help here would be greatly appreciated!!

Answer 1

Use intnx() to align both the datetime of interest and today's datetime to the start of the week.

proc sql;
    create table want as
        select *
        from table
        where intnx('dtweek', date, 0, 'B') = intnx('dtweek', datetime(), 0, 'B')
   ;
quit;

Answer 2

As others have pointed out - if you are using SQL pass-thru, you need to use date functions that exist in your "flavor" of SQL. SAS specific functions will not work, and in particular SAS function "today()" has no meaning in the SQL you are working with.

The approach I would take is:

1. in a SAS datastep - get today's date
2. use today's date to calculate beginning and end of the week
3. convert beginning/end dates to character strings (string will depend on how dates are formatted in your sql database - date or datetime)
4. use character strings to create macro variables
5. feed macro variables into sql pass-thru query to subset dates wanted

Below is some example code. It might not get you all the way there, but could give you some more ideas to try.

data have;
    today = today();    *** TODAYs DATE ***;
    wkday = weekday(today); *** WEEK DAY NUMBER FOR TODAY, POSSIBLE VALUES ARE 1-7 ***;

    start = today - (wkday - 1);    *** CALCULATE SUNDAY ***;
    end = today + (7 - wkday);  *** CALCULATE SATURDAY ***;

    *** UNCOMMENT AND USE BELOW IF WEEK START/END IS MON-FRI ***;
    *start = today - (wkday - 2);   *** CALCULATE MONDAY ***;
    *end = today + (6 - wkday); *** CALCULATE FRIDAY ***;

    *** REPRESENT DATES AS DATE-TIME CHARACTER STRING - SURROUNDED BY SINGLE QUOTES ***;
    cstart = "'" || put(start, date9.) || ' 00:00:00.0000' || "'";
    cend =  "'" || put(end, date9.) || ' 00:00:00.0000' || "'";

    *** USE CHARACTER VARIABLES TO CREATE MACRO VARIABLES ***;
    call symput('start', cstart);
    call symput('end', cend);
run; 


*** IN SQL PASS-THRU, USE MACRO VARIABLES IN WHERE STATEMENT TO SUBSET ONE WEEK ***;
Proc Sql
    connect to odbc (environment=x user=y p=z);
      create table basic.curweek as select * from connection to odbc
           (select year, month, week, store, sales, SKU
                from datatable
                where (&start. <= week and week <= &end.)
                order by sku);
     disconnect from odbc;
quit;