BookmarkSubscribeRSS Feed
Rahul_B
Obsidian | Level 7

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.

3 REPLIES 3
Patrick
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 3011 views
  • 0 likes
  • 2 in conversation