fedsql date comparison against macro variable

acordes — Mon, 14 Mar 2022 15:32:40 GMT

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;

Re: fedsql date comparison against macro variable

yabwon — Mon, 14 Mar 2022 16:25:23 GMT

Could you share some more log?

topic fedsql date comparison against macro variable in SAS Programming

fedsql date comparison against macro variable

Re: fedsql date comparison against macro variable