topic date handling in fedsql in SAS Programming

date handling in fedsql

acordes — Wed, 09 Mar 2022 09:46:40 GMT

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;

Re: date handling in fedsql

acordes — Wed, 09 Mar 2022 10:00:14 GMT

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"

Re: date handling in fedsql

FreelanceReinh — Wed, 09 Mar 2022 12:31:18 GMT

Hello @acordes,

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

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

Re: date handling in fedsql

Kurt_Bremser — Wed, 09 Mar 2022 12:44:35 GMT

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

Re: date handling in fedsql

acordes — Wed, 09 Mar 2022 13:50:00 GMT

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.