How to transfer the sql code (working) to its fedsql silbing?
proc sql;
create table sum22 as
select a.*, b.fini, put(a.year,4.) as _year
from PUBLIC.ALL_MONTHLY_COPY a join
(select sum(fin) as fini, from_brand, year from
PUBLIC.ALL_MONTHLY_COPY where month(mes) <= month(intnx('month', today(), -7, 'end')) and
lowcase(producto) <> 'other' AND (VN_VN ='10' or VN_VN = '11') group by from_brand, year
) b
on a.from_brand=b.from_brand and a.year=b.year;
quit;
But in fedsql it gives an ERROR: Function MONTH(DOUBLE) does not exist so I circumvent to this unefficient code.
proc cas;
source MPG_toyota;
create table public.sum22{options replace=true} as
select a.*, b.fini, put(a.year,4.) as _year
from PUBLIC.ALL_MONTHLY_COPY a join
(select sum(fin) as fini, from_brand, year from
PUBLIC.ALL_MONTHLY_COPY where month(mes) <= month(intnx('month', today(), -7, 'end')) and
lowcase(producto) <> 'other' AND (VN_VN ='10' or VN_VN = '11') group by from_brand, year
) b
on a.from_brand=b.from_brand and a.year=b.year ;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;
data _null_;
format _fecha_cierre date9.;
_fecha_cierre=intnx('month', today(), -7, 'end');
call symputx("fecha_cierre", _fecha_cierre);
run;
%let tt=%sysfunc(putn(&fecha_cierre., month.));
%put &fecha_cierre. &tt.;
proc cas;
source MPG_toyota;
create table public.sum22{options replace=true} as
select a.*, b.fini, put(a.year,4.) as _year
from PUBLIC.ALL_MONTHLY_COPY a join
(select sum(fin) as fini, from_brand, year from
PUBLIC.ALL_MONTHLY_COPY where month(mes) <= &tt. and
lowcase(producto) <> 'other' AND (VN_VN ='10' or VN_VN = '11') group by from_brand, year
) b
on a.from_brand=b.from_brand and a.year=b.year ;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;
