date handling in fedsql

[acordes]

These 2 approaches works:

proc cas;
    source MPG_toyota;
        select distinct producto, from_brand, count(*) as cnt
        from MKT.CARTERA_NODUP where date '2022-02-01' <= _fecformo and _fecformo <= date '2022-02-24'   
        group by producto, from_brand having from_brand in ('Volkswagen', 'Audi')  ;
    endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

%let date1='2022-02-01';
proc cas;
    source MPG_toyota;
        select distinct producto, from_brand, count(*) as cnt
        from MKT.CARTERA_NODUP where date &date1 <= _fecformo and _fecformo <= date '2022-02-24'   
        group by producto, from_brand having from_brand in ('Volkswagen', 'Audi')  ;
    endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

But this fails:

I don't now how to single quote correctly the character converted date expression. 

proc cas;
    source MPG_toyota;
        select distinct producto, from_brand, count(*) as cnt
        from MKT.CARTERA_NODUP where date '2022-02-01' <= _fecformo and _fecformo <= date "'"||put(today(), yymmdd10.)||"'"   
        group by producto, from_brand having from_brand in ('Volkswagen', 'Audi')  ;
    endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

[acordes]

It does not even work when I create the char date variables before in a data step.

data step:
help_date=quote(put(today(), yymmdd10.), "'");
help_date1=quote(put(today(), yymmdd10.));
help_date2=put(today(), yymmdd10.);

proc cas;
    source MPG_toyota;
        select distinct producto, from_brand, count(*) as cnt
        from MKT.CARTERA_NODUP where date '2022-02-01' <= _fecformo and _fecformo <= date help_date2
        group by producto, from_brand having from_brand in ('Volkswagen', 'Audi')  ;
    endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;

It says ERROR: Syntax error at or near "HELP_DATE2"

[Kurt_Bremser]

Why don't you keep everything as SAS dates and avoid the typecast?

In the data step

help_date2 = today();

and in the SQL

where '01feb2022'd <= _fecformo and _fecformo <= help_date2

[acordes]

You're right for this example. 

But the need arose when I wanted to run in fedsql what I had done before in sql. 

And in this query I hard-coded the date range and I begun to put '01feb2022'd within the fedsql source code. That works for sql but not for fedsql...

If I have the date as numeric in my table I can use it without problems. 

[FreelanceReinh · Accepted solution]

Hello @acordes,

My SAS installation doesn't include PROC CAS, so I can't test my suggestion:

%sysfunc(quote(%sysfunc(today(),yymmdd10.),%str(%')))