topic PROC FEDSQL macro facility using 'into' doesnt works same as proc sql in Developers

PROC FEDSQL macro facility using 'into' doesnt works same as proc sql

Rahul_B — Fri, 08 Feb 2019 15:45:21 GMT

proc fedsql does not support macro 'into' facilty unlike the proc sql.

PROC FEDSQL sessref=mySession ;
SELECT MAX(CONTACT_KEY)+1 INTO :V_CONTACT_KEY FROM caslib.CI_CUST_CONTACT_HISTORY;
quit;

PROC sql ;
SELECT MAX(CONTACT_KEY)+1 INTO :V_CONTACT_KEY FROM oralib.CI_CUST_CONTACT_HISTORY;
quit;

does anyone know how to max value from caslib dataset and store it in macro ? I am aware that it can be done with

%let ( ofcourse ) and CALL SYMPUT, but with proc fedsql it isnt working.

Re: PROC FEDSQL macro facility using 'into' doesnt works same as proc sql

Patrick — Sun, 10 Feb 2019 10:36:40 GMT

There are no macros in CAS but couldn't you just add a Create View to your current syntax in FedSql and then use this view in a SAS data NULL step writing your one record into a macro using call symputx() - haven't done something like this yet but I believe such an approach should pull your data from CAS to the SPRE environment where you've still got "traditional" SAS.

Re: PROC FEDSQL macro facility using 'into' doesnt works same as proc sql

Rahul_B — Tue, 12 Feb 2019 09:44:06 GMT

Hi Patrick,

You cannot create a view using fedsql in CAS,i was unable to pull data from CAS to SPRE evnrionment.alternatively you can create a CAS/Inmemory Table instead and load the max value in that table, but my need is to get max key from 250 Million records ( which is CAS table) and this took 48 mins.

So what i did was i store the max key value in variable in the CAS table , and used Call Symput() to call it in the macro, where i wanted.

I have never used fedsql to perform a max operation in a CAS,but suprising while i did run fedsql it was scanning 250 Million records it took 48 mins, that is not something i would expect with inmemory tables, they are suppose to fast in processing.

Re: PROC FEDSQL macro facility using 'into' doesnt works same as proc sql

Patrick — Tue, 12 Feb 2019 11:37:55 GMT

@Rahul_B

Thanks for your feedback. Yep, I'm also only starting to get into Viya and FedSQL and there is definitely much to learn - workarounds included...