SAS proc sql passthrough running indefinitely

Question

I have some data in week-date-time format ie 14dec2020:00:00:00:000. I am using SAS and a proc sql (SQL Server) pass through 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.

Data macros;
    today = today();
    wkday = weekday(today);
    start = today()-(wkday-1);
    end = today+(7-wkday);
    length cstart cend $22;
    cstart = "'" || put(start, date9.) || ':00:00:00.000' || "'";
    cend = "'" || put(end, date9.) || ':00:00:00.000' || "'";
    call symput ('start', cstart);
    call symput('end', cend);
run;
proc sql;  
    connect to odbc (dsn='x' uid='y' pwd='z');
    create table work.pleasehelp 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;

There macros data set returns the proper date boundaries but when it tries running the PROC SQL it runs indefinitely. I ran it for 16 minutes and it only had a CPU run time of 1.09 seconds on it. I've tried changing the cstart and cend to just start and end throughout the queries but that runs with no matches.

Any help here would be appreciated!

What is the pass through running in ? Oracle, SQL Server, MySQL, etc... — Richard
and what are the values in datatable columns year month and week ? Are they SQL Server date types who values are the start of the represented interval ? Is datatable a view ? What result set do you see if you execute the same passthrough query in a tool such a SQL Server Management Studio (SSMS) ? — Richard
Sorry for the delayed response here with the holidays but year is a four digit number, month is a digit 1-12 and then week is the 14dec2020:00:00:00:000 mentioned above — BluesGotTheCup

Richard Richard · Accepted Answer · 2020-12-30T23:10:44

This sample code will LOG what the SAS generated pass through query is. Try performing the same query is a SQL Server tool.

%let start=;
%let end=;

data _null_;
    last_sunday = intnx('week', today(), 0);
    next_sunday = last_sunday + 7;

    call symput('last_sunday', quote(put(last_sunday, yymmdd10.),"'")); 
    call symput('next_sunday', quote(put(next_sunday, yymmdd10.),"'")); 
run;

/* The source code in the macro variables is
   'yyyy-mm-dd'
   and will be understood by SQL Server to be an ISO8601 standard date value
 */
%put &=last_sunday;
%put &=next_sunday;

/* This macro has SQL NOEXEC so you can see in the LOG window what
   the pass through query is.  Try running the same query in a
   SQL Server tool to see what the expected result set is
 */

%macro query;

proc sql noexec;
    connect to odbc (dsn=x uid=y pwd=z);
    create table work.pleasehelp as
    select * from connection to odbc
    ( select Year, Month, Week, store, sales, SKU
      from datatable
      where &last_sunday <= Week and Week < &next_sunday
            order by SKU
        );
    disconnect from odbc;
quit;

%mend;

options mprint;
%query;

Log window

MPRINT(QUERY):   create table work.pleasehelp as select * from connection to odbc (

select Year, Month, Week, store, sales, SKU from datatable where '2020-12-27' <= Week and Week < '2021-01-03' order by SKU

 );
NOTE: Statement not executed due to NOEXEC option.

SAS proc sql passthrough running indefinitely

1 Answers