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;
PROC sql ;
SELECT MAX(CONTACT_KEY)+1 INTO :V_CONTACT_KEY FROM oralib.CI_CUST_CONTACT_HISTORY;
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.
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.
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.
SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
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.