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;
Hello @acordes,
My SAS installation doesn't include PROC CAS, so I can't test my suggestion:
%sysfunc(quote(%sysfunc(today(),yymmdd10.),%str(%')))
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"
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
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.
Hello @acordes,
My SAS installation doesn't include PROC CAS, so I can't test my suggestion:
%sysfunc(quote(%sysfunc(today(),yymmdd10.),%str(%')))
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.