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

[Rahul_B]

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.

[Patrick]

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.

[Rahul_B]

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.

[Patrick]

@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...