Instead of directly searching in the SAS Information Catalog, we can use an intelligent agent, powered by an LLM, to examine a catalog extract. This extract, a CSV file, houses metrics about cataloged tables and files obtained from the SAS Information Catalog through the Catalog REST API. This indirect approach provides meaningful insights about the Catalog's assets. As we'll explore, there are potential advantages to using an LLM. It allows for follow-up questions based on the answers received and enables output formatting according to the user's preference.   For clarity, the LLM denotes GPT-4 from Azure OpenAI, while the Catalog represents the SAS Information Catalog.   In brief, the steps to use a LLM to search through cataloged assets are as follows:   Catalog caslibs or SAS libraries using the SAS Information Catalog. Acquire a SAS Viya access token. Export the metrics into a CSV file. Develop an intelligent agent using LangChain and Python (code provided in this post). Query the CSV file (watch the videos in this post).   The following diagram highlights well the different components:     Disclaimer: the image was generated using my personal ChatGPT Plus subscription.   Video   Watch the following videos, where we explore the approach.   Same Results   The comparison is carried out through several examples where tables are queried using specific keywords. Both methods demonstrate similar proficiency in finding tables with column keywords such as 'temple' and 'brussels', and even those in different languages. Whether it's searching tables by table keywords, filtering by data quality completeness less than 95%, or identifying columns that contain content in Mandarin, the search results from both methods are closely matched.     Different Results   However, there are instances when the results differ, such as when querying for columns with private data or columns semantic types.   This discrepancy occurs because the catalog uses facets for search, while the LLM searches for a keyword across all cataloged facets.   The flexibility of the LLM becomes apparent, especially when it comes to customizing the query output.   LLM also excels in managing free text searches, such as locating references to specific keywords or when utilizing Chinese characters (汉字 Hànzì).     Findings:   The formulation of your prompt is crucial. Different prompts may yield different results. A lack of results does not necessarily mean that what you're searching for is absent from the catalog or the raw data!   Queries   Below are a few queries and their results using either the catalog search or the LLM query.   Same Results   Prompt: How many unique tables have data quality completeness less than 0.95 or 95%? What is their name? Catalog: 16 results.     LLM: 16 results.     Different Results   Prompt: List the Library – the Table – the Column – semantic type, where the information privacy of the column is private (private data was found in that column).   Catalog: 20 results.      LLM: 15 results. The difference can be attributed to the manner in which we phrase the question. If we aim to identify all columns with private data, tables may be repeated. In contrast, the catalog displays all tables with at least one column containing private data. The LLM search should yield more results. However, it doesn't. Could this be due to token limits affecting the completeness of the output?     Prompt: Which tables contain columns semantic type cataloged as ‘place’?   Catalog: 7 results.     LLM: 12 results. This is intriguing and can be attributed to the fact that 'place' is searched across various fields, not just confined to the semantic type column.     Python Program   Here is the Python program that defines the LangChain intelligent agent, which interacts with the SAS Information Catalog:   # The program allows you to chat with a CSV file. # Install packages pip install -U python-dotenv langchain langchain-openai langchain_experimental langchain_core pandas tabulate # Import Azure OpenAI from langchain_openai import AzureChatOpenAI import os # Import Azure OpenAI variables from .env file. This file is assumed present in the same folder with this program. ## Set environment variables needed by LangChain from dotenv import load_dotenv load_dotenv() azure_oai_endpoint = os.getenv("AZURE_OAI_ENDPOINT") azure_oai_key = os.getenv("AZURE_OAI_KEY") azure_oai_deployment = os.getenv("AZURE_OAI_DEPLOYMENT") azure_oai_model = os.getenv("AZURE_OAI_DEPLOYMENT") os.environ["OPENAI_API_TYPE"] = "azure" os.environ["AZURE_OPENAI_API_KEY"] = azure_oai_key os.environ["AZURE_OPENAI_ENDPOINT"] = azure_oai_endpoint os.environ["OPENAI_API_VERSION"] = "2023-12-01-preview" # Define LLM llm = AzureChatOpenAI(deployment_name=azure_oai_deployment, temperature=0, max_tokens=2000) print (llm) # CSV file - the catalog download my_csv_file = 'catalog_download.csv' # Use CSV DataFrame agent in LangChain from langchain.agents.agent_types import AgentType from langchain_experimental.agents.agent_toolkits import create_csv_agent # Set up the prompt and memory from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", """Answer the questions step by step. The history of the messages is critical and important to use. """ ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser from langchain_core.messages import AIMessage, HumanMessage chat_history = [] agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_function_messages( x["intermediate_steps"] ), "chat_history": lambda x: x["chat_history"], } | prompt | llm | OpenAIFunctionsAgentOutputParser() ) agent = create_csv_agent( llm, my_csv_file, verbose=True, agent_type=AgentType.OPENAI_FUNCTIONS) # Ask questions print ('Start chatting with your file / table...\n') # Continuously listens for text input to send as text to Azure OpenAI def chat_with_open_ai(): while True: print("Azure OpenAI is listening. Write 'Stop' or press Ctrl-Z to end the conversation.") try: # Get input q = input("Enter your question: \n") if q == "Stop": print("Conversation ended.") break else: print("\nI will ask: ", q) result = agent.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) except EOFError: break # Main try: chat_with_open_ai() except Exception as err: print("Encountered exception. {}".format(err))   Conclusions   This post compares the efficacy of the SAS Information Catalog search and an intelligent agent, LLM (GPT-4 from Azure OpenAI), in querying cataloged assets.   The comparison is conducted through several examples where tables are searched using specific keywords. Both systems display similar proficiency in locating tables with single keywords like 'temple', 'brussels', and even multi-lingual variations of a keyword. The same pattern is consistent when searching multiple keywords,  such as 'fun', 'entertainment', 'night', 'show', searching for tables with data quality completeness less than 95% or identifying columns cataloged as containing content in Mandarin.   However, there are instances when the results differ, such as when querying for columns with private data or columns cataloged as 'place'.   LLM also proves superior in handling free text searches, such as finding references to 'Feng Feng', 'St Patrick', or handling non-alphabetic characters.   Finally, LLM's ability to provide fresh perspectives and its flexibility is highlighted, particularly when context awareness and memory are required.   Using a Catalog extract also has its disadvantages, the most significant of which is that it becomes an offline process, disconnected from updates in the Catalog or data structure. If new data is added to the catalog or existing data is cataloged, the CSV extract needs to be updated to reflect these changes.   In conclusion, LLM's advanced capabilities make it a powerful tool for information retrieval from cataloged assets.   What to Read Next?   Discover Your Data with SAS Information Catalog APIs from Python – Access. Download Metadata and Metrics with SAS Information Catalog APIs from Python. Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain.   Acknowledgements   Thanks to Nancy Rausch and Lavanya Ganesh, for initiating the question:   Can we use an LLM to query the catalog data?     Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Navigating through a sea of data assets can be a daunting task. SAS Information Catalog is your navigator in this journey, allowing you to discover, search, and manage your SAS Viya assets efficiently. When a data asset is discovered, hundreds of metrics are calculated. Imagine having the ability to upload these metrics to a CAS table, using a Catalog API. This opens up the possibility of using these rich metrics in custom reports or flows.   Metadata Levels   When utilizing the SAS Information Catalog REST API to upload metadata and metrics for data assets, tables, or files, you have several options to choose from. The metadata can be of various types, such as:   dataDictionary: provides basic column-level metadata, such as name and type. dataDictionaryAndProfile: offers extensive metadata that combines both data dictionary and profile metrics. detailedMetrics: gives comprehensive metadata at a column level, including patterns, frequency distribution values.   The middle level, dataDictionaryAndProfile, strikes an excellent balance between richness and complexity, making it the ideal choice if you need to identify private data, semantic type or classification at a column level.   Upload Metadata   Get a SAS Viya Access Token   For information on obtaining a SAS Viya access token, refer to the previous post Discover Your Data with SAS Information Catalog APIs from Python – Access.   Python Program   The Python program upload_metadata.py retrieves metrics and metadata from SAS Information Catalog. It then uploads the metrics and metadata to a CAS table, using the Catalog REST API. The program:   Imports necessary packages. Constructs the Catalog API URL for the request. Retrieves a saved access token. Sends the Catalog API request: Headers 'Content-Type' include the keyword 'instance.upload'. Body contains the caslib name and a CAS table prefix.   Here’s the complete code:   # 1 Packages import sys import requests import json import os # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments") print ("Argument List:", str(sys.argv) + '\n') baseURL=str(sys.argv[1]) search_query=str(sys.argv[2]) pem_path=str(sys.argv[3]) # 3. Construct Variables print ("REST API Inputs:\n") print('\nYour SAS VIYA host is ', baseURL) url = f'{baseURL}/catalog/instances' + search_query print('\nCatalog API URL: ', url) # 4. Get the Saved Access Token print('\nRetrieving the saved token from api/access_token.txt\n') with open("api/access_token.txt", "r", encoding="UTF-8") as f: token = f.read() #print(token) print('\nUpload metadata specified in the URL\n') """ # Select one of the following - passed as a parameter # Upload profile Metrics filtered by name with prefix url = f"{baseURL}/catalog/instances/?filter=startsWith(name,'WATER')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" # Upload detailedMetrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=detailedMetrics&limit=10" # Upload profile Metrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=dataDictionary&prefix=simpleUpload&limit=100" """ headers = { 'Authorization': 'Bearer ' + token,'Content-Type': 'application/vnd.sas.metadata.instance.upload.request+json', 'Accept': 'application/vnd.sas.metadata.instance.upload.request+json'} # Replace placeholders in the body with actual values data = '''{ "level": "dataDictionaryAndProfile", "prefix": "Catalog", "dateTimeStampSuffix": false, "serverName": "cas-shared-default", "caslibName": "Public" }''' response = requests.post(url, headers=headers, data=data, verify=pem_path) print('Response code: ', response.status_code) print('\nThe Catalog Metadata was uploaded for you in CAS in Public.Catalog_DMDictionaryPlusMetrics \n') print(response.text)     This program demonstrates how to use the SAS Viya Catalog REST API to perform a metadata upload.   Run the Program   The program expects the following command-line arguments:   The hostname of the SAS Viya server. The metadata level and additional filters. The path to the PEM file for TLS certificate verification.   Metadata Level dataDictionaryAndProfile   Windows In a Bash terminal on a Windows machine, you can run the program with command-line arguments:   # Certificate on a Windows machine and executable is Python python upload_metadata.py https://sas_viya_url "?filter=startsWith(name,'WATER')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Filter The filter uploads only metadata for data assets:   Where the asset name starts with ‘WATER’. Their type = ‘cas’. This is broader than just a CAS table, it implies a data asset, a table or a file. Limit to 10 (data assets) matching the criteria. With the metadata level dataDictionaryAndProfile.   Running the program with the provided parameters will upload the metadata in an in-memory CAS table. In the request body, you can specify the CAS server, the caslib name, 'Public', and a CAS table prefix for the in-memory table, 'Catalog', in this example.   data = '''{ "level": "dataDictionaryAndProfile", "prefix": "Catalog", "dateTimeStampSuffix": false, "serverName": "cas-shared-default", "caslibName": "Public" }'''   Linux When running the program, a SAS Viya certificate is used, in the form of a PEM file. The PEM file was copied in the 'C:\Users\...\' folder.   In a Bash terminal on a Linux machine, the run statement would be:   # Certificate on a Linux machine and executable is python3 python3 download_metadata.py https://sas_viya_url "?filter=startsWith(name,'WATER')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" /home/cloud-user/.certs/gelenv_trustedcerts.pem   A SAS Viya certificate is used here, in the form of a PEM file. The PEM file is assumed to be present in the '/home/myuser/' folder.   Output   You will see many metrics both at a table and at a column level, such as:   The calculated classification. If private data was detected. Languages detected. Sentiment detected. Keywords extracted. Classic profiling measures, etc.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The table metadata, such as keywords, tags, most important columns, privacy and so on, is repeated for each column.   Conclusion   This program demonstrated how to use the SAS Viya REST API to upload in CAS a table, containing SAS Information Catalog metrics.   Acknowledgements   Nancy Rausch, R&D.   Lavanya Ganesh, R&D.   @AchalPatel , early pioneer of the Upload Catalog API.       Additional Resources   You might find the following resources helpful.   Read:   Discover Your Data with SAS Information Catalog APIs from Python – Access. Leveraging SAS® Information Catalog REST APIs: Programmatically Discovering Data. Download Metadata and Metrics with SAS Information Catalog APIs from Python. SAS Information Catalog: Administrator’s Guide.   Watch:   SAS Demo | Top Data Questions You Can Answer with SAS Information Catalog. Discover Information Assets with SAS Information Catalog | SAS Viya Quick Start Tutorial. How to Use SAS Information Catalog.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

This post introduces the GPT-4 Turbo with vision model from Azure OpenAI and explores its integration with SAS products like SAS Visual Analytics and SAS Studio. Imagine uploading a screenshot of a basic report and simply asking questions to understand its contents. Moreover, you can provide screenshots of your source files with sample data, and the GPT-4 Turbo with vision will generate the necessary SAS code to recreate the table in SAS Viya. Curious about turning a screenshot into a functional in-memory table in SAS Viya? Dive into the post and check out the accompanying video for a comprehensive guide.   Long Live LMM!   "Wait, do you mean LLM?!?"   On December 12th, Microsoft unveiled the public preview of the GPT-4 Turbo with vision model on Azure. This Large Multimodal Model (LMM), developed by OpenAI, accepts both text and image inputs, marking an evolution from the previous Large Language Models (LLMs). Within Azure AI Studio, this model can be seamlessly integrated with Azure AI's vision services, expanding its capabilities to include:   Advanced Optical Character Recognition (OCR): Extracting text, whether printed or handwritten, from images. Refined object detection: Identifying and tagging objects and providing descriptions. Improved video analysis: Monitoring video feeds for anomalies and tracking objects across video frames. Data point generation: Converting information into structured files.   And the list goes on.   Examples   To grasp the functionality of the GPT-4 Turbo with Vision model, it's best to walk through a series of practical examples. Consider this scenario in Azure OpenAI Studio: you upload a screenshot of a SAS Visual Analytics report.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   From there, you can engage with the model in various ways:   Ask for identification: "What is this?"     Request data extraction: "Please extract the report data to a CSV file, comma delimited.”     Seek financial insights: "What are some key takeaways from the report?"     With LLM or LMM models you should always double check your calculations! Don’t trust the models blindly! [says Microsoft...].   Ask strategic questions: "Based on the gross profit margin – which one product would you discontinue and why?"     The response to such a question can be complex and controversial. For instance, if sofas show a low gross profit margin, it might suggest discontinuing them. However, consider the broader impact—what if sofas draw customers into the store who then purchase additional items?   My perspective is that the model's output should be viewed as a reflection in a mirror—it shows only what is directly in front of it. Similarly, the output from a model should serve as a tool to inform decision-making, not replace it.   The final decision rests on human shoulders.   From Screenshots to Table   Ready to take a bold step forward? Can you turn a few screenshots into a fully functional in-memory table in SAS Viya? Watch the video for a demonstration of the process.     I trust that I've demonstrated the art of the possible—converting images into a target table, it's entirely achievable.   Required Resources   SAS Viya deployment. Azure subscription with access to Azure OpenAI GPT-4 Turbo with Vision. Currently, the Azure OpenAI resource can be deployed to a list of select locations. GPT-4 model vision-preview version deployed. Azure Computer Vision resource (optional, helps with OCR). Your images and your prompts.   System Message   "You are an AI assistant that helps people build data structures. Users will submit images of their target table, data set or report. You will ask them what the source data looks like. Users must respond by submitting images, describing each source table or paste a few columns with sample data. You will then generate the SAS code needed to realize the data structure. You must breakdown the task into steps, then detail what operations are required: loading files, joins, filters, sorts, summarizations, etc. At the end, the target table must be loaded in-memory, in CAS as a global promoted table."   Prompts   Define your objective: “I need to produce the data used by the following SAS Visual Analytics report:” Provide the model with source information: The following images describe my source files:     Force the model to extract text from images: “for the load step, can you extract the ‘/path/to' from the images and add it in the code (hint: the path is indicated in the bottom left corner)?" CAS prompt: "The last step, promote… table in CAS will likely result in an error. The table must be loaded from WORK to the CASUSER caslib. Here’s an example, please refactor the code: <your code here>".   Automation   You can script the whole approach using the OpenAI Python client library. To access the images, you can use a URL or, encode the image using base64 and pass it to the Azure OpenAI API.   Conclusions   Screenshots to target table? Possible with SAS Viya and Azure OpenAI GPT-4 vision. Deploy the resources, upload a few screenshots of your target report, your source files and ask the model to generate the code to create the table underlying the report.   Additional Resources   Getting Started with Multimodality and Getting started with Azure GPT-4-Turbo Vision by Valentina Alto. GPT-4 and GPT-4 Turbo Preview model availability. Multimodal Conversational Interfaces with GPT and Vision AI by Scott Holden – Microsoft AI Summit, Melbourne, March 2024.   What to Read Next?   SAS Code Generation and Execution in SAS Viya with Azure OpenAI. GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach. How to Create Your Custom LangChain Agent for SAS Viya. Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Read the post to understand how you can create your own SAS code Generator-Executor, which uses Generative AI to interact with SAS Viya for data management tasks. The system takes prompts, converts them into SAS code, and executes this code in a user-defined SAS Viya environment. The results are then displayed, and users can ask follow-up questions or receive more information.   The Big Picture   This custom agent integrates multiple technologies, including SAS Viya, Azure OpenAI, and open-source packages like SASPY and LangChain.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The Generator-Executor (GenEx) approach is innovative in that it leverages a large language model (LLM), GPT-4, from Azure OpenAI, to generate SAS code based on user prompts.   The generated SAS code is sent to an executor tool.   The executor tool uses SASPY to connect to a SAS Viya instance. The executor submits the SAS code generated by the model. The SAS code is executed in a SAS Studio compute context.   The response comes back within seconds, validating the code generated by the model.   I am excited about this approach, because of its simplicity. Only two custom tools are needed, the generator and the executor.   By simplifying code generation and execution, it can save time and resources, and make data management more accessible to non-technical users, reducing the need for manual coding and potentially speeding up data management tasks.   Video   Watch the following video, where we explore the approach:     The approach is an evolved variation of the custom agent discussed in two earlier posts:   GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach. How to Create Your Custom LangChain Agent for SAS Viya.   The main difference is that in the current approach, we let the LLM generate the SAS code. Whereas in the above posts, we had to create one custom tool for each operation. And that was unscalable. It didn't keep me up at night, but it preoccupied me. I asked myself if there's a better approach.   The spark to try something different was ignited after I read Understanding the Magic: Deconstructing Langchain’s SQL Agent. I was incredulous when Luc explained that the all-powerful SQL Database Toolkit only has four tools. I was expecting thousands of lines of code. Instead, I found four tools with well-crafted prompts and descriptions.   That somehow taught me a valuable lesson. In the era of LLMs, we have to think differently and program accordingly. We have to take advantage of the "plasticity" of the model by adjusting the prompts or the system messages (prompt engineering).     Python Program   The main Python program defines the LangChain custom agent that interacts with SAS Viya:   # LangChain custom SAS Viya agent with memory and only two tools # Resource: https://python.langchain.com/docs/modules/agents/how_to/custom_agent import os import json import urllib3 urllib3.disable_warnings # Load the Large Language Model - from Azure OpenAI from langchain_openai import AzureChatOpenAI ## Read the Azure OpenAI environment variables from a separate file import config os.environ["OPENAI_API_TYPE"] = "azure" os.environ["AZURE_OPENAI_API_KEY"] = config.api_key os.environ["AZURE_OPENAI_ENDPOINT"] = config.azure_openai_endpoint os.environ["OPENAI_API_VERSION"] = config.api_version ## We'll use GPT-4 as deployed model llm = AzureChatOpenAI(deployment_name="gpt-4", temperature=0, max_tokens=2000) # Define custom tools ## SASPY - see https://communities.sas.com/t5/SAS-Communities-Library/Submit-workloads-to-SAS-Viya-from-Python/ta-p/807263 from langchain.agents import tool, Tool @tool def generate_sas_code(prompt: str) -> int: """Returns the generated SAS code, based on the user prompt. Useful when people ask to generate SAS code that can be executed in a SAS environment or session. The response is returned as pure SAS code, executable in a SAS session. That means any explanation is commented out in SAS like fashion /* comment */""" import os from openai import AzureOpenAI ## Setting Azure OpenAI environment variables import config os.environ["OPENAI_API_TYPE"] = "azure" os.environ["AZURE_OPENAI_API_KEY"] = config.api_key os.environ["AZURE_OPENAI_ENDPOINT"] = config.azure_openai_endpoint os.environ["OPENAI_API_VERSION"] = config.api_version # define client client = AzureOpenAI( azure_endpoint = os.getenv("AZURE_OPENAI_ENDPOINT"), api_key=os.getenv("AZURE_OPENAI_KEY"), api_version="2023-12-01-preview" ) #print("\nI will ask: ", prompt, '\n') response = client.chat.completions.create( model="gpt-4", # model = "deployment_name". messages=[ {"role": "system", "content": """You are a SAS assistant that helps people write SAS code. The response you return has to be pure SAS code, executable in a SAS session. That means any explanation must be commented out in SAS like fashion. For example /* comment */ or * comment; . Explain your reasoning."""}, {"role": "user", "content": "Return detailed table description for a SAS table CLASS in SASHELP library."}, {"role": "assistant", "content": "PROC CONTENTS DATA=SASHELP.CLASS;run;quit;"}, {"role": "user", "content": "Describe the SAS Data Set group from the health SAS library."}, {"role": "assistant", "content": """"proc datasets library=health nolist; contents data=group (read=green) out=grpout; title 'The Contents of the GROUP Data Set';run;quit;"""}, {"role": "user", "content": prompt}, ] ) sas_code = response.choices[0].message.content + '\n' return sas_code def execute_sas_code(sas_code: str) -> str: """Useful when you need to execute generated SAS code. Input to this tool is correct SAS code, ready to be executed a SAS environment or session. Output comes in two parts: 1. Results: the SAS code execution results comes at the top. The results are easy to identify. They can be found after the key word 'Results ---' . 2. Log: The SAS code execution log comes after the results. The results are easy to identify. They can be found after the key word 'Log --- '. Identify the results, summarize and provide a short explanation. If the execution result is not correct, an error message will be returned by the SAS code execution log. Error is signaled by the keyword 'ERROR'. If an error is returned, you may need to rewrite the SAS code, using the generate SAS code tool. The execute the SAS code. Provide a Final Result: Summarize and provide a short explanation of what was done and what the outcome was. If you encounter an issue detail what the issue is.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') # Submit SAS code and parse results and the log sas_result = sas.submit(sas_code, results='TEXT') result = json.dumps(sas_result['LST']) log = json.dumps(sas_result['LOG']) return ('Results --- \n', " ".join(result.split()), '---\n', 'Log --- \n', " ".join(log.split()), '---\n') # Describe the tools. The description is important for the LangChain chain. tools = [ Tool( name = "generate_sas_code", func = generate_sas_code, description = """ Useful when people ask to generate SAS code that can be executed in a SAS environment or session. Input to this tool is the user prompt. Breakdown the user prompt. Explain your reasoning how you are going to construct the SAS code, from the user's prompt. Return the generated SAS code, based on the user's prompt or question. The response you return has to be pure SAS code, executable in a SAS session. That means any explanation must be commented out in SAS like fashion. For example /* comment */ or * comment ; . """ ), Tool( name = "execute_sas_code", func = execute_sas_code, description = """Useful when you need to execute generated SAS code. Input to this tool is correct SAS code, ready to be executed. Output comes in two parts: 1. Results: the SAS code execution results comes at the top. The results are easy to identify. They can be found between Results --- and --- . 2. Log: The SAS code execution log comes after the results. The results are easy to identify. They can be found between Log --- and --- . Identify the results, summarize and provide a short explanation. Provide a Final Result: Summarize and provide a short explanation of what was done and what the outcome was. If you encounter an issue detail what the issue is.""" ) ] # Create Prompt ## Now let us create the prompt. Because OpenAI Function Calling is finetuned for tool usage, we hardly need any instructions on how to reason, or how to output format. ## We will just have two input variables: input and agent_scratchpad. input should be a string containing the user objective. ## agent_scratchpad should be a sequence of messages that contains the previous agent tool invocations and the corresponding tool outputs. # Add Memory ## Add a place for memory variables to go in the prompt ## Keep track of the chat history ## First, let’s add a place for memory in the prompt. We do this by adding a placeholder for messages with the key "chat_history". Notice that we put this ABOVE the new user input (to follow the conversation flow). from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", """You are a world class Data Engineer, who can write and execute SAS code. Please make sure you complete the objective above with the following rules: 1/ Your job is to first breakdown the topic into relevant questions for understanding the topic in detail. You should have at max only 3 questions not more than that, in case there are more than 3 questions consider only the first 3 questions and ignore the others. 2/ If asked to generate SAS code invoke the generate_sas_code tool. 3/ If asked to run the generated code, invoke the execute_sas_code tool. Input to this tool is a detailed and correct SAS code, stripped of any plain text. If the query is not correct, an error message will be returned. If an error is returned, read the ERROR from the log. You may need to rewrite the SAS code, using the generate code tools, check the SAS code and execute the code again.""" ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) # Bind tools to LLM ## How does the agent know what tools it can use? In this case we’re relying on OpenAI function calling LLMs, which take functions as a separate argument and have been specifically trained to know when to invoke those functions. ## To pass in our tools to the agent, we just need to format them to the OpenAI function format and pass them to our model. (By bind-ing the functions, we’re making sure that they’re passed in each time the model is invoked.) from langchain_community.tools.convert_to_openai import format_tool_to_openai_function # about to be deprecated llm_with_tools = llm.bind(functions=[format_tool_to_openai_function(t) for t in tools]) # Create the Agent ## Putting those pieces together, we can now create the agent. We will import two last utility functions: a component for formatting intermediate steps (agent action, tool output pairs) to input messages that can be sent to the model, and a component for converting the output message into an agent action/agent finish. from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser ## We can then set up a list to track the chat history from langchain_core.messages import AIMessage, HumanMessage chat_history = [] agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_function_messages( x["intermediate_steps"] ), "chat_history": lambda x: x["chat_history"], } | prompt | llm_with_tools | OpenAIFunctionsAgentOutputParser() ) from langchain.agents import AgentExecutor agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True) # ASK QUESTIONS print ('Start chatting with your data...\n') # Continuously listens for text input to recognize and send as text to Azure OpenAI def chat_with_open_ai(): while True: print("SAS and Azure OpenAI are listening. Say 'Stop' or press Ctrl-Z to end the conversation.") try: q = input("Enter your question: \n") if q == "Stop.": print("Conversation ended.") break else: print("\nI will ask: ", q) result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) except EOFError: break # Main try: chat_with_open_ai() except Exception as err: print("Encountered exception. {}".format(err))   I won't go into details and explain fully the Python program. There are a few comments inside. A similar program was explained in sufficient detail in How to Create Your Custom LangChain Agent for SAS Viya.   Conclusions   The SAS code Generator-Executor (GenEx) approach is innovative in that it leverages a large language model, GPT-4, to generate SAS code based on user prompts, reducing the need for manual coding and potentially speeding up data management tasks. The key feature of the approach is that it uses only two tools: a code generator, and a code executor to submit and test the generated SAS code in a SAS Viya environment of your choice.   Additional Resources   SASPY: Submit workloads to SAS Viya from Python. LangChain Custom Agent. GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach. How to Create Your Custom LangChain Agent for SAS Viya. Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Navigating through a sea of data assets can be a daunting task. SAS Information Catalog is your navigator in this journey, allowing you to discover, search, and manage your SAS Viya assets efficiently. When a data asset is discovered, hundreds of metrics are calculated. Imagine having the ability to access these metrics through a simple command, downloading them using a Catalog API. This opens up the possibility of using these rich metrics in custom reports or flows to answer pressing questions.   Metadata Levels   When utilizing the SAS Information Catalog REST API to download metadata for data assets, tables, or files, you have several options to choose from. The metadata can be of various types, such as:   dataDictionary: provides basic column-level metadata, such as name and type. dataDictionaryAndProfile: offers extensive metadata that combines both data dictionary and profile metrics. detailedMetrics: gives comprehensive metadata at a column level, including patterns, frequency distribution values.   The middle level, dataDictionaryAndProfile, strikes an excellent balance between richness and complexity, making it the ideal choice if you need to identify private data, semantic type or classification at a column level.   Download Metadata   Recorded Demonstration   For a more visual understanding of the process, you can watch the following recorded demonstration:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Get a SAS Viya Access Token   For information on obtaining a SAS Viya access token, refer to the previous post Discover Your Data with SAS Information Catalog APIs from Python – Access.   Python Program   The Python program download_metadata.py retrieves and processes metadata from a SAS Viya Information Catalog, using REST API:   Imports necessary packages. Constructs Catalog API URL for the request. Retrieves a saved access token. Sends the Catalog API request. Saves the response to a CSV file.   Here’s the complete code:   # 1 Packages import sys import requests import json import os # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments") print ("Argument List:", str(sys.argv) + '\n') baseURL=str(sys.argv[1]) search_query=str(sys.argv[2]) pem_path=str(sys.argv[3]) # 3. Construct Variables print ("REST API Inputs:\n") print('\nYour SAS VIYA host is ', baseURL) print('\nCatalog API URL: ') print(f'{baseURL}/catalog/instances' + search_query) url = f'{baseURL}/catalog/instances' + search_query print('\nDownload metadata specified in the URL: ' + url + '\n') """ # URL Examples: Select one of the following - passed as a parameter # Download profile Metrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=startsWith(name,'WATER')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" # Download detailedMetrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=detailedMetrics&limit=10" # Download profile Metrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=dataDictionary&prefix=simpledownload&limit=100" """ # 4. Get the Saved Access Token print('\nRetrieving the saved token from api/access_token.txt\n') with open("api/access_token.txt", "r", encoding="UTF-8") as f: token = f.read() #print(token) headers = {'Accept': 'text/csv','Authorization': 'Bearer ' + token} # Create folder directory = 'api' if not os.path.exists("api/"): os.mkdir("api/") # Save in catalog_download.csv file utf-8 ## stream the API requests with open(directory + '/catalog_download.csv', 'w', encoding="utf-8") as f: with requests.get(url, headers=headers, stream=True, verify=pem_path) as response: f.write(response.text) f.close() print('\nThe Catalog Downloaded Metadata was stored for you as ' + directory + '/catalog_download.csv\n')   This program demonstrates how to use the SAS Viya REST API to perform a metadata download. It provides a basic example and can be used as a starting point for building more advanced functionality.   Run the Program   The program expects the following command-line arguments:   The hostname of the SAS Viya server. The metadata level and additional filters. The path to the PEM file for TLS certificate verification.   Metadata Level dataDictionaryAndProfile   In a Bash terminal on a Windows machine, you can run the program with command-line arguments:   # Certificate on a Windows machine and executable is python python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Running the program with the provided parameters will download the metadata and save it in a CSV file.   The filter downloads only metadata for data assets:   Where name starts with ‘detailed’ Their type = ‘cas’. This is broader than just a CAS table, it implies a data asset, a table or a file. Limit to 10 (data assets) matching the criteria. With the metadata level dataDictionaryAndProfile.   A SAS Viya certificate is used here, in the form of a PEM file. The PEM file was copied in the 'C:\Users\...\' folder.   In a Bash terminal on a Linux machine, the above statement would be:   # Certificate on a Linux machine and executable is python3 python3 download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" /home/cloud-user/.certs/gelenv_trustedcerts.pem   A SAS Viya certificate is used here, in the form of a PEM file. The PEM file is assumed to be present in the '/home/myuser/' folder.   Output You will see many valuable characteristics at a column level, such as:   The calculated classification. If private data was detected in the column. Languages detected. Sentiment detected. Keywords extracted. Classic profiling measures, etc.     The table metadata, such as table keywords, tags, most important columns, privacy and so on, is repeated for each column.   Metadata Level dataDictionary   In a Bash terminal on a Windows machine, you can run the program with command-line arguments.   python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionary&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Output You shall see simple column metadata. The table metadata is repeated for each column.     Metadata Level - detailedMetrics   In a Bash terminal on a Windows machine, you can run the program with command-line arguments.   python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=detailedMetrics&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Output You will see many values and detailed characteristics at a column - metric - value level.     Conclusion   This program demonstrated how to use the SAS Viya REST API to download the metadata at column level. Stay tuned for more in-depth discussions and tutorials in this series.   Acknowledgements   Nancy Rausch, R&D.   Lavanya Ganesh, R&D.   @AchalPatel, early pioneer of the Download Catalog API.    Additional Resources   You might find the following resources helpful. Read:   SAS Information Catalog: Administrator’s Guide. Discover Your Data with SAS Information Catalog APIs from Python – Access. Leveraging SAS® Information Catalog REST APIs: Programmatically Discovering Data.   Watch:   SAS Demo | Top Data Questions You Can Answer with SAS Information Catalog. Discover Information Assets with SAS Information Catalog | SAS Viya Quick Start Tutorial. How to Use SAS Information Catalog.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Navigating through a sea of data assets can be a daunting task. SAS Information Catalog is your navigator in this journey, allowing you to discover, search, and manage your SAS Viya assets efficiently. When a data asset is discovered, hundreds of metrics are calculated. Imagine accessing these metrics with a simple command, downloading them, or even using them in a report or a flow. This blog series will guide you through the process of leveraging the SAS Information Catalog REST APIs from Python to learn more about your SAS Viya data assets. This post, in particular, will focus on acquiring an access token using a method revised in 2023.     SAS Information Catalog in Two Minutes   If you're new to SAS Information Catalog, here's a quick rundown. If you prefer to watch a video, check out SAS Demo | Top Data Questions You Can Answer with SAS Information Catalog or How to Use SAS Information Catalog. If you prefer to read, the catalog: Allows you to discover, search, and manage your SAS Viya assets, such as data, tables, files, SAS Visual Analytics reports, SAS models, SAS decisions, SAS Studio flows, or code files. You can search the catalog for your assets using keywords (free text search) or calculated metrics and their values (faceted search). You can also view the information privacy status of your assets, which is calculated by the catalog. The catalog produces numerous metrics about your data assets at both the table and column levels. Agents, a set of jobs, drive the discovery process. They list the contents of a library, apply any filters, and then start calculating the metrics. These metrics are then sent to an index that you can use for searching.   Why Use Catalog APIs?   Despite having comprehensive interfaces in SAS Information Catalog, there might be a need for custom reporting or workflows based on specific asset characteristics. SAS Information Catalog APIs, now made public, are the solution to this challenge.   Get an Access Token   Despite the availability of resources, getting a SAS Viya access token can sometimes be confusing and many users get stuck at this stage. The SAS Software GitHub page explains a simple method to get an access token to work with SAS Viya REST APIs.   However, there's a more secure and efficient method, described by Joe Furbee in Register Client and Create Access Token. I prefer this method as it excludes the password from the Python program, making the program more "Git-ready". The method also registers a client, uses an authorization code, and a TLS certificate to secure data in transit.   Recorded Demonstration   For a more visual understanding of the process, you can watch the following recorded demonstration:     Python Program Here’s the complete code to get an access token and a refresh token. # Run instructions """ Using Joe Furbee's approach: https://github.com/sassoftware/rest-api-use-cases/blob/main/python/authentication/registerClient_generateToken_Python_2023.ipynb https://blogs.sas.com/content/sgf/2023/02/07/authentication-to-sas-viya/ print('\nGetting parameters from the command line\n') # 1 Packages import requests, json, os, base64, sys # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments\n") #print ("Argument List:", str(sys.argv)) baseURL=str(sys.argv[1]) client_id=str(sys.argv[2]) client_secret=str(sys.argv[3]) admin_user=str(sys.argv[4]) admin_password=str(sys.argv[5]) pem_path=str(sys.argv[6]) print() print ("\nWe are going to use a certificate: ", pem_path) # 3. Construct Variables authUri="/SASLogon/oauth/token" print('\nYour SAS VIYA host is ', baseURL) print ('\nAuth URL', authUri) # Get a BEARER_TOKEN to delete and register a client print('\nGet a BEARER_TOKEN to register a client\n') # generate API call for register access token url = f"{baseURL}/SASLogon/oauth/token" payload = f"grant_type=password&username={admin_user}&password={admin_password}" headers = { 'Content-Type': 'application/x-www-form-urlencoded', 'Authorization': 'Basic c2FzLmNsaTo=' } # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) register_access_token = json.loads(response.text)['access_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) headers = { 'Content-Type': 'application/json', 'Authorization': "Bearer " + register_access_token } """ # Delete the client if it already exists print() print(f"Delete the client {client_id} if it exists, using the BEARER_TOKEN") print() print(baseURL, "client", client_id) response = requests.request("DELETE", f"{baseURL}/SASLogon/oauth/clients/{client_id}", headers=headers, verify=pem_path) print(json.dumps(response.json(), indent=4, sort_keys=True)) """ # Register the client using the BEARER_TOKEN print(f"\nRegister the client {client_id} using the BEARER_TOKEN\n") # create API call payload data payload='{"client_id": "' + client_id +'","client_secret": "'+ client_secret +'","scope": ["openid", "uaa.user"],"authorized_grant_types": ["authorization_code","refresh_token"],"redirect_uri": "urn:ietf:wg:oauth:2.0:oob","access_token_validity": "43199"}' # generate API call for register access token url = f"{baseURL}/SASLogon/oauth/clients" # REGISTER THE CLIENT process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create an access token using the client and an authorization code print('\nCreate an access token using the client and an authorization code\n') # create the authorization url codeURL = baseURL + "/SASLogon/oauth/authorize?client_id=" + client_id + "&response_type=code" # encode the client string client_string = client_id + ":" + client_secret message_bytes = client_string.encode('ascii') base64_bytes = base64.b64encode(message_bytes) base64_message = base64_bytes.decode('ascii') # prompt with instructions and entry for auth code print(f"* Please visit the following site {codeURL}") print("* If provided a login prompt, add your SAS login credentials") print("* Once authenticated, you'll be redirected to an authorization screen, check all of the boxes that appear") print("* This will result in a short string of numbers and letters such as `VAxVFVEnKr`; this is your authorization code; copy the code") code = input("Please enter the authorization code you generated through the previous instructions, and then press Enter: ") # generate API call for access token url = f"{baseURL}/SASLogon/oauth/token#authorization_code" payload = "grant_type=authorization_code&code=" + code headers = { 'Accept': 'application/json', 'Content-Type': 'application/x-www-form-urlencoded', 'Authorization': "Basic " + base64_message } # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) access_token = json.loads(response.text)['access_token'] refresh_token = json.loads(response.text)['refresh_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create directory #directory = os.getcwd() directory = 'api' if not os.path.exists("api/"): os.mkdir("api/") # Create access_token.txt file with open(directory + '/access_token.txt', 'w') as f: f.write(access_token) print('\nThe access token was stored for you as ' + directory + '/access_token.txt\n') # Create refresh_token.txt file with open(directory + '/refresh_token.txt', 'w') as f: f.write(refresh_token) print('\nThe refresh token was stored for you as ' + directory + '/refresh_token.txt\n') Steps in the "2023" method:   Get a BEARER_TOKEN to register (or delete) a client. Delete the client if it exists - commented out, as not relevant when running a first time. Register the client gel_app. Create an access token using the registered client and an authorization code. Visit a web-page, select the scopes and generate the authorization code. Paste the authorization code and get an access token and a refresh token.   Please note, you must remove statements that print the token from your production code. Those are there just for debugging.   Run the Program   In a Bash terminal on a Windows machine, you can run the program with the parameters you've set.   SASUSER=fill_in_here # SAS user SASPASS=fill_in_here # SAS user password CLIENTSECRET=fill_in_here # client secret # Certificate on a Windows machine and executable is python python get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS 'C:\Users\myuser\Downloads\gelenv_trustedcerts.pem'   A SAS Viya certificate is used here, in the form of a .pem file. The .pem file was copied in the 'C:\Users\...\' folder.   In a Bash terminal on a Linux machine, the above statement would be:   SASUSER=fill_in_here # SAS user SASPASS=fill_in_here # SAS user password CLIENTSECRET=fill_in_here # client secret # Certificate on a Linux machine and executable is python3 python3 get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS /home/my_user/.certs/gelenv_trustedcerts.pem   A SAS Viya certificate is used here, in the form of a .pem file. The .pem file is assumed to be present in the '/home/myuser/' folder.   Refresh Token   Access tokens have a 1-hour time-to-live (ttl) by default, so it's common to use a refresh token to generate a new access token. The refresh token itself has a 90-day ttl, while the authorization code is good for 30 minutes and single use. The access token generated can be transferred to other notebooks, programs and used for external API calls.   Get an Access Token with a Refresh Token   You can use a refresh token to get an access token when the latter expires.   # 1 Packages import requests, json, os, base64, sys # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments") #print ("Argument List:", str(sys.argv)) baseURL=str(sys.argv[1]) client_id=str(sys.argv[2]) client_secret=str(sys.argv[3]) pem_path=str(sys.argv[4]) print ("\nWe are going to use a certificate: ", pem_path) # encode client string client_string = client_id + ":" + client_secret message_bytes = client_string.encode('ascii') base64_bytes = base64.b64encode(message_bytes) base64_message = base64_bytes.decode('ascii') # 3. Construct Variables authUri="/SASLogon/oauth/token#refresh_token" print('Your SAS VIYA host is ', baseURL, '\n') print ('\nRefresh the access token by using the refresh token\n') url = baseURL + authUri # read the refresh token directory = 'api' print('\nRetrieving the saved refresh token\n') with open("api/refresh_token.txt", "r", encoding="UTF-8") as f: refresh_token = f.read() #print(refresh_token) payload = "grant_type=refresh_token&refresh_token=" + refresh_token headers = { 'Content-Type': 'application/x-www-form-urlencoded', 'Accept': 'application/json', 'Authorization': "Basic " + base64_message } response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) #print(response.text) # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) access_token = json.loads(response.text)['access_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create access_token.txt file with open(directory + '/access_token.txt', 'w') as f: f.write(access_token) print('\nThe access token was stored for you as ' + directory + '/access_token.txt\n')   Please note, you must remove statements that print the token. Those are there just for debugging.   Run the Program   In a Bash terminal on a Windows machine, you can run the program with the parameters you've set.   Note that you no longer need the user and the password with the refresh token method. The client, the secret and the refresh token itself are enough to get you an access token. In all the subsequent posts, we will use the refresh token to get a new access token.   CLIENTSECRET=fill_in_here # client secret # Certificate on a Windows machine and executable is python python refresh_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET 'C:\Users\myuser\Downloads\gelenv_trustedcerts.pem'   In a Bash terminal on a Linux machine, you can run the program with the parameters you've set.   CLIENTSECRET=fill_in_here # client secret # Certificate on a Linux machine and executable is python3 python get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS /home/my_user/.certs/gelenv_trustedcerts.pem   Conclusions   Learning how to harness the power of the SAS Information Catalog APIs can unlock a new level of efficiency and customization in managing your SAS Viya assets. By calling them from Python you can open the door to new integrations. This tutorial on acquiring an access token is the first step in that journey. Stay tuned for more in-depth discussions and tutorials in this series.   What to Read Next   Read the following post  in the series: Download Metadata and Metrics with SAS Information Catalog APIs from Python.   Acknowledgements   Thanks to my colleaque @joeFurbee  for all his work in this space.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

In a previous post, GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach, we discussed an agent using GPT-4, LangChain, and SASPY for data management in SAS Viya. This agent performed tasks like getting table info, column details, and table manipulation. Now, we'll discuss how to create such a smart agent.   The Big Picture   In simple terms, LangChain matches the user's intent with the best custom tool. This tool then interacts with SAS Viya.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   After this, the log is returned and a response is created by the large language model (LLM). If you haven't already, check out the video in the previous article, GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach, for more information.     Now let's look at what you need to build this custom agent.   Components   SAS Viya Environment   You will need a SAS Viya deployment. From your SAS Viya deployment you will need: The SAS Viya URL. A SAS username. The SAS user's password.   Azure OpenAI   You need to create a resource and deploy a model. To make a call successfully against Azure OpenAI, you need an endpoint, a key, and a deployment. Visual Studio Code   If you're a regular user of Visual Studio Code, great.   Python Requirements   Create a requirements.txt file that includes these Python packages:   saspy langchain langchain_openai pandas   Azure OpenAI Configuration   Create a 'config.py' file in a folder to hold the Azure OpenAI configuration.   azure_openai_endpoint =  'https://resource-name.openai.azure.com/' # your endpoint should look like https://YOUR_RESOURCE_NAME.openai.azure.com/ api_key = 'fill_in_here' # your key looks like 932e...a99 api_version = '2023-07-01-preview'   You'll refer back to this program later in the sastools_mem.py program using 'import config'.    Setup SASPY   We'll use SASPY to send programs from a Python program to a SAS Viya instance. We'll use SASPY in the custom SAS Viya tools that we'll create in the main Python program. Ajmal Farzam has already given a brief explanation of SASPY in his article Submit workloads to SAS Viya from Python. I've used his guidelines to create two files:   sascfg_personal.py   Create a sascfg_personal.py file that contains a profile, the SAS Viya URL and the name of the authkey, among other details.   SAS_config_names=['httpsviya'] SAS_config_options = {'lock_down': False,                       'verbose'  : True,                       'prompt'   : True                      } SAS_output_options = {'output' : 'html5'}       # not required unless changing any of the default httpsviya = {'ip'      : 'my_sas_viya_URL.sas.com',              'context' : 'SAS Studio compute context',              'authkey' : 'HTTP_Dev',              'options' : ["fullstimer", "memsize=4G"]              }   authinfo   SASPY is reading the credentials from an authinfo file:   Windows: Create a _authinfo file in C:\Users\myuser. The first word must match the authkey in the sascfg_personal.py file.   HTTP_Dev user SAS_USER password SAS_Pass_Value   Linux: you should create a .authinfo file in /home/myuser. Read more about it in the SASPY documentation / authinfo.   Optional: Create a Program to Test SASPY Connection   For example:   import sys sys.path import saspy # display active session sas = 'No active SAS session!' print(sas) # create _authinfo file in C:\Users\sbxbot if sas == 'No active SAS session!': sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') else: print ('There is a SAS session') print(sas) print(type(sas))   LangChain Custom Agent for SAS Viya Operations   The sastools_mem.py is our main Python program. It makes a LangChain custom agent that interacts with SAS tables in SAS Viya, carries out specific tasks, and provides answers based on those tasks.   # LangChain custom SAS Viya agent WITH memory ############################################## # Resource: https://python.langchain.com/docs/modules/agents/how_to/custom_agent # Load the LLM import os import urllib3 urllib3.disable_warnings ## A Large Language Model - Azure OpenAI from langchain_openai import AzureChatOpenAI ## Setting Azure OpenAI environment variables import config os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["AZURE_OPENAI_ENDPOINT"] = config.azure_openai_endpoint os.environ["OPENAI_API_VERSION"] = config.api_version ## GPT-4 as deployment llm = AzureChatOpenAI(deployment_name="gpt-4", temperature=0, max_tokens=2000) # Define tools # SAS - see https://communities.sas.com/t5/SAS-Communities-Library/Submit-workloads-to-SAS-Viya-from-Python/ta-p/807263 # non-SAS, such as get_word_length for control from langchain.agents import tool, Tool @tool def get_word_length(word: str) -> int: """Returns the length of a word. Useful when you need to calculate the length of a word or string. This will return the length as an integer.""" return len(word) def get_table_information(library_table: str) -> str: """Returns detailed table description for a SAS table. Accepts two inputs, table and library. Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the columns, the number of rows, statistics about the table.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') table = library_table.strip().split('.')[-1] library = library_table.strip().split('.')[0] sas_code = f"PROC CONTENTS DATA={library}.{table};run;quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG']) def get_column_info_table(library_table: str) -> str: """Returns information about the columns in a SAS table. Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the result of PROC CONTENTS on the table.""" # SASPY auth and start session import saspy table = library_table.split('.')[-1] library = library_table.split('.')[0] sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') table = sas.sasdata(table, library) # DESCRIBE TABLE return (table.columnInfo()) def append_table(library_table: str) -> str: """Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') base = library_table.strip().split(',')[0] appended = library_table.strip().split(',')[-1] sas_code = f"data sasdm.new; set {base};run; proc append base=sasdm.new data={appended} force; run; quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG']) tools = [ Tool( name = "get_word_length", func = get_word_length, description = "Useful when you need to calculate the length of a word or string. This will return the length." ), Tool( name = "get_table_information", func = get_table_information, description = "Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the columns, the number of rows, statistics about the table." ), Tool( name = "get_column_info_table", func = get_column_info_table, description = "Useful when you need to get detailed information about a SAS table, at a column level. This will return the columns, the number of rows, statistics about the table." ), Tool( name = "append_table", func = append_table, description = "Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library." ) ] # Create Prompt ## Now let us create the prompt. Because OpenAI Function Calling is finetuned for tool usage, we hardly need any instructions on how to reason, or how to output format. ## We will just have two input variables: input and agent_scratchpad. input should be a string containing the user objective. ## agent_scratchpad should be a sequence of messages that contains the previous agent tool invocations and the corresponding tool outputs. # Add Memory ## Add a place for memory variables to go in the prompt ## Keep track of the chat history ## First, let’s add a place for memory in the prompt. We do this by adding a placeholder for messages with the key "chat_history". Notice that we put this ABOVE the new user input (to follow the conversation flow). from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", """You are a world class Data Engineer, who can do detailed research using SAS. Please make sure you complete the objective above with the following rules: 1/ Your job is to first breakdown the topic into relevant questions for understanding the topic in detail. You should have at max only 3 questions not more than that, in case there are more than 3 questions consider only the first 3 questions and ignore the others. 2/ You should use the get table information or get column info tools to get more information about a given topic. You are allowed to use these tools only 3 times in this process. 3/ Perform the operations demanded by the user, such as append, merge, etc. using the appropriate tools.""", ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) # Bind tools to LLM ## How does the agent know what tools it can use? In this case we’re relying on OpenAI function calling LLMs, which take functions as a separate argument and have been specifically trained to know when to invoke those functions. ## To pass in our tools to the agent, we just need to format them to the OpenAI function format and pass them to our model. (By bind-ing the functions, we’re making sure that they’re passed in each time the model is invoked.) from langchain_community.tools.convert_to_openai import format_tool_to_openai_function llm_with_tools = llm.bind(functions=[format_tool_to_openai_function(t) for t in tools]) # Create the Agent ## Putting those pieces together, we can now create the agent. We will import two last utility functions: a component for formatting intermediate steps (agent action, tool output pairs) to input messages that can be sent to the model, and a component for converting the output message into an agent action/agent finish. from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser ## We can then set up a list to track the chat history from langchain_core.messages import AIMessage, HumanMessage chat_history = [] agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_function_messages( x["intermediate_steps"] ), "chat_history": lambda x: x["chat_history"], } | prompt | llm_with_tools | OpenAIFunctionsAgentOutputParser() ) from langchain.agents import AgentExecutor agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True) # ASK QUESTIONS ## Control q = 'How many letters in the word SASDM?' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'Is that a real word?' print('\nI will ask: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) ## SAS Viya q = 'Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library?' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'Are there any columns containing date or period of time?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) q = 'Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'How many rows again?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) q = 'Provide detailed table description for table NEW from SASDM library' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'In what library is stored that table?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history})   Importing Libraries   Initially, the script imports necessary Python libraries and loads the Large Language Model (LLM) from LangChain. AzureChatOpenAI from the langchain_openai library is used as the LLM. The script sets up the necessary environment variables for Azure OpenAI using a config file.     import os import urllib3 from langchain_openai import AzureChatOpenAI import config ...   Defining Tools   The script defines several tools, which are Python functions to perform certain tasks. These tools help in working with SAS tables. They can fetch table information, fetch column information, and append tables.   from langchain.agents import tool, Tool @tool def get_word_length(word: str) -> int: """Returns the length of a word.""" ... def get_table_information(library_table: str) -> str: """Returns detailed table description for a SAS table.""" ... def get_column_info_table(library_table: str) -> str: """Returns information about the columns in a SAS table.""" ... def append_table(library_table: str) -> str: """Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') base = library_table.strip().split(',')[0] appended = library_table.strip().split(',')[-1] sas_code = f"data sasdm.new; set {base};run; proc append base=sasdm.new data={appended} force; run; quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG'])   The append_table tool does the following: It imports SASPY. It starts a SAS session by linking to the SAS Studio compute context, which was defined in the sascfg_personal.py file. It parses the 'library_table' variable, whose value is given by the LLM. It executes the SAS code with the parsed arguments, in a SAS session SASPY creates.   Note: We specify the name of the table to be created, just to verify the results. The name can be another parameter, sure.   Creating the Agent   After defining the tools, the script creates an agent using the LangChain API. This agent uses the OpenAI GPT-4 model and is linked with the tools defined earlier. The agent gets a prompt template which guides it on how to interact with the user.   from langchain_community.tools.convert_to_openai import format_tool_to_openai_function ... from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser ... from langchain.agents import AgentExecutor ...   Running the Agent   Finally, the script runs the agent by asking a series of questions. There are SAS and non-SAS questions. The latter are used for control:   How many letters in the word SASDM? (non-SAS) Is that a real word? (non-SAS) Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library? Are there any columns containing date or period of time? Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library How many rows again? Provide detailed table description for table NEW from SASDM library In what library is the table stored?   The function agent_executor.invoke() is used to communicate with the agent and obtain its responses. The chat history is stored and used as context for future questions.   from langchain_core.messages import AIMessage, HumanMessage ... q = 'Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library' print('\nI will ask: ', q, '\n') ... result = agent_executor.invoke({"input": q, "chat_history": chat_history}) ... q = 'How many rows again?' agent_executor.invoke({"input": q, "chat_history": chat_history})   Summary   In short, this Python script sets up a custom LangChain agent. This agent can handle detailed operations on SAS tables and respond to user questions based on those operations.   Run the Python Program   In a Bash terminal type:   pip install -r requirements.txt python sastools_mem.py   Interpreting the Execution Logs   This section provides an extract of the full log generated by the running Python program:   I will ask: Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library? > Entering new AgentExecutor chain... Invoking: `get_column_info_table` with `SASHELP.PRDSAL2` SAS server started using Context SAS Studio compute context with SESSION_ID=4768a27c-0826-496e-8693-c67e26bc5aab-ses0000 Member Num Variable Type Len Pos Format Informat Label 0 SASHELP.PRDSAL2 4.0 ACTUAL Num 8.0 0.0 DOLLAR12.2 NaN Actual Sales 1 SASHELP.PRDSAL2 1.0 COUNTRY Char 10.0 48.0 $CHAR10. NaN Country 2 SASHELP.PRDSAL2 3.0 COUNTY Char 20.0 80.0 $CHAR20. NaN County 3 SASHELP.PRDSAL2 10.0 MONTH Num 8.0 32.0 MONNAME3. NaN Month 4 SASHELP.PRDSAL2 11.0 MONYR Num 8.0 40.0 MONYY. MONYY. Month/Year 5 SASHELP.PRDSAL2 5.0 PREDICT Num 8.0 8.0 DOLLAR12.2 NaN Predicted Sales 6 SASHELP.PRDSAL2 6.0 PRODTYPE Char 10.0 100.0 $CHAR10. NaN Product Type 7 SASHELP.PRDSAL2 7.0 PRODUCT Char 10.0 110.0 $CHAR10. NaN Product 8 SASHELP.PRDSAL2 9.0 QUARTER Num 8.0 24.0 8. NaN Quarter 9 SASHELP.PRDSAL2 2.0 STATE Char 22.0 58.0 $CHAR22. NaN State/Province 10 SASHELP.PRDSAL2 8.0 YEAR Num 8.0 16.0 4. NaN YearThe SAS table PRDSAL2 from the SASHELP library has the following columns: 1. ACTUAL: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the actual sales. 2. COUNTRY: This is a character column with a length of 10. It uses the $CHAR10. format and represents the country. 3. COUNTY: This is a character column with a length of 20. It uses the $CHAR20. format and represents the county. 4. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 5. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 6. PREDICT: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the predicted sales. 7. PRODTYPE: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product type. 8. PRODUCT: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product. 9. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 10. STATE: This is a character column with a length of 22. It uses the $CHAR22. format and represents the state/province. 11. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. > Finished chain. I will ask to test memory: Are there any columns containing date or period of time? > Entering new AgentExecutor chain... Yes, there are columns containing date or period of time in the PRDSAL2 table from the SASHELP library. These columns are: 1. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 2. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 3. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 4. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. I will ask: Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library > Entering new AgentExecutor chain... Invoking: `append_table` with `SASHELP.PRDSAL2, SASHELP.PRDSAL3` SAS server started using Context SAS Studio compute context with SESSION_ID=8ecc570f-0f01-4153-9e32-59da3d37d7ab-ses0000 12 ods listing close;ods html5 (id=saspy_internal) options(bitmap_mode='inline') device=svg style=HTMLBlue; ods graphics on / 12 ! outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: sashtml.htm 13 14 data sasdm.new; set SASHELP.PRDSAL2;run; NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set SASDM.NEW has 23040 observations and 11 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 626.15k OS Memory 17764.00k Timestamp 01/23/2024 01:51:06 AM Step Count 3 Switch Count 0 Page Faults 0 Page Reclaims 168 Page Swaps 0 Voluntary Context Switches 76 Involuntary Context Switches 0 Block Input Operations 32 Block Output Operations 5512 14 ! proc append base=sasdm.new data= SASHELP.PRDSAL3 force; run; NOTE: Appending SASHELP.PRDSAL3 to SASDM.NEW. WARNING: Variable DATE was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable MONYR was not found on DATA file. NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: There were 11520 observations read from the data set SASHELP.PRDSAL3. NOTE: 11520 observations added. NOTE: The data set SASDM.NEW has 34560 observations and 11 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 715.50k OS Memory 18020.00k Timestamp 01/23/2024 01:51:06 AM Step Count 4 Switch Count 0 Page Faults 0 Page Reclaims 101 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 288 Block Output Operations 2960 14 ! quit; 15 ods html5 (id=saspy_internal) close;ods listing; 16 The tables PRDSAL2 and PRDSAL3 from the SASHELP library have been successfully appended. The new table is stored in the SASDM library and is named "NEW". It contains 34560 observations and 11 variables. Please note that the variable "DATE" was not found in the PRDSAL2 table and the variable "MONYR" was not found in the PRDSAL3 table. > Finished chain. I will ask to test memory: How many rows again? > Entering new AgentExecutor chain... The new appended table contains 34560 rows. > Finished chain. I will ask: Provide detailed table description for table NEW from SASDM library > Entering new AgentExecutor chain... Invoking: `get_table_information` with `SASDM.NEW` SAS server started using Context SAS Studio compute context with SESSION_ID=7b7f0494-181a-496c-a2f3-35cb4605c742-ses0000 12 ods listing close;ods html5 (id=saspy_internal) options(bitmap_mode='inline') device=svg style=HTMLBlue; ods graphics on / 12 ! outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: sashtml.htm 13 14 PROC CONTENTS DATA=SASDM.NEW;run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.03 seconds user cpu time 0.03 seconds system cpu time 0.00 seconds memory 1937.50k OS Memory 19300.00k Timestamp 01/23/2024 01:51:44 AM Step Count 3 Switch Count 0 Page Faults 0 Page Reclaims 671 Page Swaps 0 Voluntary Context Switches 3 Involuntary Context Switches 3 Block Input Operations 0 Block Output Operations 8 14 ! quit; 15 ods html5 (id=saspy_internal) close;ods listing; 16 The table NEW from the SASDM library has the following details: 1. ACTUAL: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the actual sales. 2. COUNTRY: This is a character column with a length of 10. It uses the $CHAR10. format and represents the country. 3. COUNTY: This is a character column with a length of 20. It uses the $CHAR20. format and represents the county. 4. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 5. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 6. PREDICT: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the predicted sales. 7. PRODTYPE: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product type. 8. PRODUCT: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product. 9. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 10. STATE: This is a character column with a length of 22. It uses the $CHAR22. format and represents the state/province. 11. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. The table has a total of 34560 observations. > Finished chain. I will ask to test memory: In what library is stored that table? > Entering new AgentExecutor chain... The table "NEW" is stored in the SASDM library. > Finished chain. SAS server terminated   You can watch the video in GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach to follow the execution logs.   Appending Tables   The program is able to append the table PRDSAL2 from the SASHELP library to the table PRDSAL3 from the SASHELP library. The operation is successful and the new table, named "NEW", is stored in the SASDM library. It has 34560 observations and 11 variables. **Invoking: append_table with SASHELP.PRDSAL2, SASHELP.PRDSAL3**   The tables PRDSAL2 and PRDSAL3 from the SASHELP library have been successfully appended. The new table is stored in the SASDM library and is named "NEW". It contains 34560 observations and 11 variables. Please note that the variable "DATE" was not found in the PRDSAL2 table and the variable "MONYR" was not found in the PRDSAL3 table. > Finished chain. I will ask to test memory: How many rows again? > Entering new AgentExecutor chain... The new appended table contains 34560 rows. > Finished chain.   Conclusions   The implementation of a custom SAS Viya agent in LangChain is successful, demonstrating the potential of using LLMs and specific tools to create intelligent, interactive agents. The next steps could involve expanding the range of tools available to the agent and optimizing the agent’s memory component to handle more complex interactions.   Additional Resources   SASPY: Submit workloads to SAS Viya from Python. GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach. Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   What To Read Next   Want to view a demonstration of this custom agent? View the video in GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach.    Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

This post explores an alternative approach to data management in SAS Viya using a custom LangChain agent. This agent enables users to engage in a conversational interface with SAS Viya, bypassing the need for complex programming. By leveraging the power of large language models (LLMs) and SASPY, the agent offers a variety of data management tasks that include fetching table information, retrieving column details, and manipulating tables. What's more, the agent's built-in memory feature allows for the context-aware handling of user queries and the reusability of responses for subsequent questions. This approach opens the doors to an era of enhanced user interaction and automation in data management tasks, making it a valuable tool for data engineers and analysts alike.   Earlier, I explored employing LangChain with structured data:   From SAS data sets in Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. From Azure SQL databases in Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   I was excited by the possibilities. I was equally disappointed that there was no LangChain agent for SAS Viya.   Therefore, I wrote one. The post explains the result.   The Big Picture   Watch the following video. The code executed is a Python program that defines a custom agent for interacting with SAS Viya. It leverages the LangChain Python library, Azure OpenAI, and SASPY to interact with SAS libraries and perform various operations such as fetching table information, getting column information, and appending tables.   The Finer Picture   The custom agent is primarily designed to interact with a Large Language Model (LLM), specifically, the GPT-4 model deployed on Azure OpenAI. It utilizes a set of custom defined tools to retrieve and manipulate data in SAS libraries. These tools use SASPY to connect to a SAS Viya instance, start a session and execute SAS code. It also features a memory component to remember previous interactions, which aids in maintaining context across a series of questions or commands.   Key Components   The program consists of several key components:   LLM Setup: The LLM is set up using AzureChatOpenAI from the langchain_openai Python module. The environment variables for Azure OpenAI are set, and the GPT-4 model is selected for deployment. Tool Definitions: A series of functions (tools) are defined for various tasks, such as fetching table and column info, or appending tables in SAS. These tools are then bound to the LLM. The LLM has to use them. The SAS Viya tools were created using the approach described on the LangChain Custom Agent page. I am using the getting word length tool as a control function, to sepaarate SAS from non-SAS tools. Chat Prompt Creation: A ChatPromptTemplate is created, which sets up the structure of the conversation, including the system prompts, user inputs, agent's scratchpad, and chat history. Agent Creation: An agent is created that takes user input and chat history, applies the prompt template, executes the LLM with the tools, and parses the output. Agent Execution: The agent is executed on a series of questions or commands, and the responses are stored in the chat history to maintain context.   How The Components Work Together   The agent operates by taking a user question or command, applying the prompt template, and executing the LLM with the relevant tools. The output is parsed and returned to the user. The chat history maintains a record of previous interactions, which allows the agent to refer back to earlier responses, ensuring continuity and context across multiple user inputs.   The Importance of the Approach   This approach is critical for creating intelligent, interactive agents that can handle complex tasks and maintain context across multiple interactions. By leveraging LLMs, the agent can understand and respond to a wide range of queries or commands. The use of specific tools enables the agent to interact with SAS libraries, making it a useful tool for data engineers and analysts working with SAS.   What the Program Brings to the Table   This program demonstrates how to create a powerful, interactive custom agent for SAS Viya capable of performing complex tasks and maintaining context across multiple interactions. It showcases the potential of LLMs in creating intelligent agents and the use of SASPY in interacting with SAS Viya. SASPY is just an example. The same approach could be employed with SWAT, SASCTL, or plain SAS Viya REST API.   Conclusions and Next Steps   The implementation of the custom SAS Viya agent in LangChain is successful, demonstrating the potential of using LLMs and specific tools to create intelligent, interactive agents. The next steps could involve expanding the range of tools available to the agent and optimizing the agent's memory component to handle more complex interactions.   My Own Opinion About the Approach   Personally, I believe this approach represents an exciting advancement in the field of data management. By using AI and natural language processing to make SAS Viya more accessible to non-technical users, making it easier to use, we're opening up new possibilities for data analysis and manipulation. I'm excited to see where this technology takes us in the future.   In a next post, I will describe in more detail the Python program and the different prerequisites.    What To Read Next   Want to understand what it takes to build a custom agent? Read the post How to Create Your Custom LangChain Agent for SAS Viya. Acknowledgements   Thanks to @AjmalFarzam  for teaching me SASPY in 15 minutes with his Submit workloads to SAS Viya from Python.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     ... View more

In the realm of Large Language Models (LLMs), the ability to chat with unstructured text data has been well-established. But what if we could extend this capability to interact with structured data? What if an AI could write SQL queries and even perform table joins to answer your data related questions? This is not a mere SQL fiction. With LangChain, a framework for developing applications powered by language models, and Azure OpenAI, this is reality.   To illustrate this, consider the structured data stored in an Azure SQL database. This database houses two tables: the 'Titanic' dataset from Kaggle and an invented 'Social' table filled with additional information. These tables can be joined using a common key.   LangChain, with its SQL agent, can connect to the database and execute actions. Combined with the GPT-4 model deployed in an Azure OpenAI resource, it can answer questions by executing SQL queries, even figuring out how to join the two tables if necessary.   To demonstrate, consider a Python program run from Visual Studio Code. This program sends questions to the LangChain agent and waits for an answer. The objective is to determine if the LangChain SQL agent can join two tables to retrieve the correct answer.   Sounds like SQL-Fiction? Watch the video:     The chain of thought triggered by the question: "What is the Name of the passenger that has a Middle Status and has survived?"     The Python Script """ This script connects to a SQL database and runs a few basic queries. It also initializes an Agent for chat interaction with the database. """ import os from sqlalchemy import create_engine from sqlalchemy.engine.url import URL from langchain.sql_database import SQLDatabase from langchain.chat_models import AzureChatOpenAI from langchain.agents.agent_toolkits import SQLDatabaseToolkit from langchain.agents import AgentExecutor from langchain.agents.agent_types import AgentType from langchain.agents import create_sql_agent import config # Set up database connection parameters username = "myuser" os.environ["SQL_SERVER_USERNAME"] = username server_short = "mysqlsrv" os.environ["SQL_SERVER"] = server_short server = "mysqlsrv.database.windows.net" os.environ["SQL_SERVER_ENDPOINT"] = server password = "*********" os.environ["SQL_SERVER_PASSWORD"] = password database ="geldb" os.environ["SQL_SERVER_DATABASE"] = database driver = "{ODBC Driver 18 for SQL Server}" # Set up SQLAlchemy connection db_config = { 'drivername': 'mssql+pyodbc', 'username': os.environ["SQL_SERVER_USERNAME"] + '@' + os.environ["SQL_SERVER"], 'password': os.environ["SQL_SERVER_PASSWORD"], 'host': os.environ["SQL_SERVER_ENDPOINT"], 'port': 1433, 'database': os.environ["SQL_SERVER_DATABASE"], 'query': {'driver': 'ODBC Driver 18 for SQL Server'} } db_url = URL.create(**db_config) db = SQLDatabase.from_uri(db_url) # Set up Azure OpenAI os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version llm = AzureChatOpenAI(deployment_name="gpt-4", temperature=0, max_tokens=4000) # Set up SQL toolkit for LangChain Agent toolkit = SQLDatabaseToolkit(db=db, llm=llm) # Initialize and run the Agent agent_executor = create_sql_agent( llm=llm, toolkit=toolkit, verbose=True, agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION, ) # Ask Questions agent_executor.run("how many tables can be joined? by what key?") # ... Add more questions here ...   In this script, several operations are performed: Connection credentials for an Azure SQL database are set up. A connection is established using SQLAlchemy, a SQL toolkit and Object-Relational Mapping (ORM) system for Python. An Azure OpenAI connection to a GPT-4 deployment is set up, defining the LLM in the toolkit. A SQLDatabaseToolkit is used to allow the LLM to interact with the database. An AgentExecutor is created to execute the SQL queries. Questions are sent. A chain of actions, observations, thoughts and more actions is initiated until a final answer. Prerequisites: An Azure subscription. An Azure SQL DB deployed, and data loaded in the SQL DB. Access to Azure OpenAI. An Azure OpenAI resource with a model deployed. The demo used a gpt-4model. Python (version 3.7.1 or later). LangChain and SQLAlchemy libraries. ODBC Driver for SQL Server. Conclusions   Overall   The results are impressive. The LangChain agent successfully identifies a common key and joins two tables to retrieve the required information. The accuracy of the results depends on the question asked and the keywords used. For example, “combine” which semantically is remarkably similar to “join” produced the best result.   Column Selection: Metadata Is Important   The SQL agent utilizes table metadata to determine which columns can be used in a join, thereby underscoring the importance of metadata and data quality. As the LLM leverages the database schema, table and column names, data types, and frequency distributions to make a column selection, the metadata becomes crucial. I think in the future, we will be forced to choose well the column names, the descriptions, if we want to use AI agents to probe the data.   Code Generation: Brute-Force That Works   When it comes to code generation, the LangChain agent prompts the LLM to create SQL queries. These queries are then tested against the database until a successful query is achieved. Even in case of a SQL syntax error, the agent feeds the error back into the LLM, which then refines the SQL query, exemplifying an effective 'brute-force' approach.   Dangerous Inferences: Taking Shortcuts   It seems that, whether human or machine, the tendency to take shortcuts to avoid effort or mental fatigue is a shared trait. If an AI learnt from code thrown at it, it also learnt to take shortcuts, just like we do when we program. Interestingly, the LLM is capable of inferring that two columns contain similar information, even if the data types are different. The inference was incorrect, though. Unless you know the data and you test these inferences, you will not know you have been given the wrong answer.   In conclusion, LangChain and Azure OpenAI have shown remarkable capabilities in interacting with structured data, writing SQL queries, and performing table joins. As we move forward, the importance of metadata, data quality and the ability of humans to detect logic errors, will continue to play a significant role in achieving accurate results.   References My inspiration comes from Valentina Alto's post, Building your own DB Copilot for Azure SQL with Azure OpenAI GPT-4.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

Imagine a world where you could discuss with your data as if it were a colleague. This might sound like a tech utopia, but with the power of SAS Viya, Azure OpenAI, Azure AI Speech service, LangChain, and Python programming, this is rapidly becoming a reality.     Demo One: Turning Queries into Conversations Have you ever wished you could just ask your data a question in plain English? Well, now you can.     Using the SASHELP.CARS data set as a source of data: You formulate a question using your voice. The voice is transformed into a text prompt. LangChain manages the prompt. A “thought” is formed. This “thought” then morphs into an actionable data query. The query further transforms into one or several observations. In some cases, data types are even converted on the fly from string to float, and unnecessary symbols like commas or dollar signs are removed.   Demo Two: Data Speaks Your Language English is not the only language in which you can formulate your queries. With the power of LangChain, you can also interact with your data in a language you speak, be it French, Dutch, Mandarin, Romanian or any other.     Text-to-Text and Speech-to-Speech: Two Sides of the Same Coin Text-to-text interaction, powered by Large Language Models (LLMs), has shown tremendous capabilities in handling unstructured data. When it comes to structured data, LangChain enters the fray, making it a breeze to interact with data.     Speech-to-speech interaction is another step forward. Here, your voice is captured and converted into text, and the generated response is converted back into speech.   SAS Studio: Your Data Interaction Playground When real-time interaction isn't a requirement, you can opt for a straightforward text-to-text approach. Within SAS Studio, you can call upon LangChain and Azure OpenAI with the help of PROC PYTHON.       Essential Components For text-to-text interaction, you'll need: An Azure subscription. Access to Azure OpenAI. An Azure OpenAI resource with a model deployed. The demo used a text-davinci-003 model. A data source. Python (version 3.7.1 or later). LangChain and Azure OpenAI libraries. When you are calling the Python program from SAS Studio, you must have: Python configured in SAS Viya. Python libraries pandas, openai and langchain installed. For speech-to-speech interaction, additional requirements include: Visual Studio Code with Python (version 3.7.1 or later). Python's azure-cognitiveservices-speech library. A Speech resource in your Azure subscription. Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017, 2019, and 2022. Conclusion The amalgamation of SAS Viya, Azure OpenAI, LangChain, and Python programming is opening new dimensions in data interaction. It's not just about querying anymore; it's about conversing, transforming the way we engage with our data.   Digging Deeper In Valentina's blog, to process a SQL DB table, she loaded it in a Pandas DataFrame. I applied the same concept to a SAS data set, using PROC PYTHON to load it in a DataFrame.   I am fully aware that this approach might not be suitable for tens of millions of rows. However, you could construct aggregate data sets, first. The approach was quite common in Business Intelligence or Data Warehouse projects. Then, load those aggregates in a DataFrame.   However, in the first two demos, I had to first export the SASHELP.CARS data set to a CSV file and load it into a DataFrame, on my PC. This was due to the lack of a LangChain SAS agent and toolkit for direct SAS data access, and the necessity for my Python installation to access a microphone and speaker for speech-to-text and text-to-speech capabilities.   Despite this, LangChain can be employed in the Python setup associated with SAS Viya for text-to-text conversations. You can see this in action in the SAS Studio demo.   Code Explanation The code used in the speech-to-speech approach.     Text-to-Text SAS Program You can run the following program in SAS Studio to interact with Azure OpenAI and LangChain:   filename resp clear; /* This program uses Azure OpenAI to interact with a pandas DataFrame loaded from a SAS table. It sets up a LangChain agent that can answer queries about the data in the DataFrame. Functions: ask(query): Prints a query and the agent's answer to the query. */ proc python; submit; import pandas as pd from langchain.llms import AzureOpenAI import openai import os import config from langchain.agents import create_pandas_dataframe_agent df = pd.read_sas("/opt/sas/viya/home/SASFoundation/sashelp/cars.sas7bdat") os.chdir('folder_path_where_config.py_is_stored') # config.py contains the API details: keys, version, etc. os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version llm = AzureOpenAI(deployment_name=config.api_model, model_name=config.api_model) agent = create_pandas_dataframe_agent(llm, df, verbose=True) def ask(query): print('Q: ', query) print('Final Answer: ', agent.run(query)) query = 'list the top 5 car make model pairs with most horsepower.' ask(query) endsubmit; run;   Speech-to-Speech Python Program """ This script performs speech recognition and synthesis using Azure OpenAI and Azure Speech Service. It loads a CSV file into a pandas DataFrame and sets up an agent to interact with the data using Azure OpenAI. The script listens to user voice input, converts it to text, sends the text to Azure OpenAI, and synthesizes the OpenAI's response back to speech. Functions: ask_langchain(query): Sends a query to Azure OpenAI, gets the response, and synthesizes the response to speech. chat_with_open_ai(): Continuously listens for speech input, recognizes the speech, sends it to Azure OpenAI, and synthesizes the OpenAI's response back to speech """ import pandas as pd import openai import os import config from langchain.llms import AzureOpenAI from langchain.agents import create_pandas_dataframe_agent import azure.cognitiveservices.speech as speechsdk df = pd.read_csv("cars.csv") os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version os.environ["SPEECH_KEY"] = config.speech_key os.environ["SPEECH_REGION"] = config.speech_region llm = AzureOpenAI(deployment_name=config.api_model, model_name=config.api_model) agent = create_pandas_dataframe_agent(llm, df, verbose=True) speech_config = speechsdk.SpeechConfig(subscription=os.environ.get('SPEECH_KEY'), region=os.environ.get('SPEECH_REGION')) audio_output_config = speechsdk.audio.AudioOutputConfig(use_default_speaker=True) audio_config = speechsdk.audio.AudioConfig(use_default_microphone=True) speech_config.speech_recognition_language="en-US" speech_config.speech_synthesis_voice_name='en-US-JennyMultilingualNeural' speech_recognizer = speechsdk.SpeechRecognizer(speech_config=speech_config, audio_config=audio_output_config) speech_synthesizer = speechsdk.SpeechSynthesizer(speech_config=speech_config, audio_config=audio_config) def ask_langchain(query): text = agent.run(query) speech_synthesis_result = speech_synthesizer.speak_text_async(text).get() if speech_synthesis_result.reason == speechsdk.ResultReason.SynthesizingAudioCompleted: print("Speech synthesized to speaker for text [{}]".format(text)) if speech_synthesis_result.reason == speechsdk.ResultReason.Canceled: cancellation_details = speech_synthesis_result.cancellation_details print("Speech synthesis canceled: {}".format(cancellation_details.reason)) if cancellation_details.reason == speechsdk.CancellationReason.Error: print("Error details: {}".format(cancellation_details.error_details)) def chat_with_open_ai(): while True: print("Azure OpenAI is listening. Say 'Stop' or press Ctrl-Z to end the conversation.") try: speech_recognition_result = speech_recognizer.recognize_once_async().get() if speech_recognition_result.reason == speechsdk.ResultReason.RecognizedSpeech: if speech_recognition_result.text == "Stop.": print("Conversation ended.") break print("Recognized speech: {}".format(speech_recognition_result.text)) ask_langchain(speech_recognition_result.text) elif speech_recognition_result.reason == speechsdk.ResultReason.NoMatch: print("No speech could be recognized: {}".format(speech_recognition_result.no_match_details)) break elif speech_recognition_result.reason == speechsdk.ResultReason.Canceled: cancellation_details = speech_recognition_result.cancellation_details print("Speech Recognition canceled: {}".format(cancellation_details.reason)) if cancellation_details.reason == speechsdk.CancellationReason.Error: print("Error details: {}".format(cancellation_details.error_details)) print("Did you set the speech resource key and region values?") except EOFError: break try: chat_with_open_ai() except Exception as err: print("Encountered exception. {}".format(err))   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

Have you wondered how to run, in batch mode, a SAS Studio flow? This post provides you the latest approach and a few useful scripts. Since the Long-Term Support (LTS) release 2023.03 (May 2023), you can use the deploy - redeploy options available in SAS Studio. These options allow users to have the control over the code deployed. We can say SAS Studio flows execution has been made DevOps ready.   The present blog overrides an older post How to Run a SAS Studio Flow in Batch. If your sole purpose is to execute a SAS Studio flow in batch, it is no longer necessary to generate the code from a flow, as described in How To Get the Code From a SAS Studio Flow and Why It Matters or How to Get the Code From a SAS Studio Flow Using Python. Unless you have another use case that justifies it, for example versioning the code, altering it, "transporting" it, etc.   Deploy as a Job - Execute - Change - Redeploy   It's easy, watch the following video and you will see why.     Execute the Job   To execute a job, in batch, you can use the SAS Viya CLI.   Authenticate to SAS Viya and Get an Access Token   # Set Variables # SAS Viya namespace - optional, but used as a prefix elsewhere export current_namespace=gelenv export SAS_CLI_PROFILE=${current_namespace} # SAS Viya PEM file for TLS auth export SSL_CERT_FILE=~/.certs/${current_namespace}_trustedcerts.pem # user and pass USER=myuser@mytenant.com PASS=mypass # SAS Viya URL export INGRESS_URL=https://fill_in_here # SAS Viya CLI install location clidir=/opt/sas/viya/home/bin # Create a SAS Viya CLI Profile $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile set-endpoint ${INGRESS_URL} $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile toggle-color on $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile set-output fulljson # Show profile $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile show # Login in $clidir/sas-viya --profile ${SAS_CLI_PROFILE} auth login -user $USER -password $PASS unset PASS # Login succeeded. Token saved.   There are more elegant and secure approaches to managing credentials. Read further, to find out.   List the Existing Jobs   Filter by the name of the job, then retrieve the id in a variable jid.   # install jq if needed sudo apt install jq -y jid=$($clidir/sas-viya --profile ${SAS_CLI_PROFILE} --output json job requests list --filter 'eq(name,'Flow')' | jq -r '.items[]["id"]') echo "The SAS object is wrapped in this job request: $jid"   Execute the Job Request   With job requests execute, you are executing the latest version of the deployed code from your SAS Studio flow.   $clidir/sas-viya --profile ${SAS_CLI_PROFILE} job requests execute --id $jid   You must have the SAS Viya CLI installed on a machine where you will execute the commands. The CLI version 1.21.17 has been used in the examples that follow. For a complete plug-in list, see Command-Line Interface: Plug-Ins. The installation instructions can be found here: SAS® Viya®: Using the Command-Line Interface.   Managing Change   What happens if you change something in a SAS Studio flow? Save it. Redeploy it. "That's all, folks!"   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Execution Results and Logs   After posting, I received a few queries: "What about the logs, the status, the execution results? Is there a way to obtain them?"   I decided to do some extra investigations. The conclusions:   The SAS® Viya® Platform Administration documentation specifies that as of 2023.09 (September 2023) stable version:   The --wait-results option is available as of release 2.6.44 of the job CLI plug-in. You can use this option with the job requests execute and job flows execute commands.   The option enables the job CLI plug-in to wait for the results from the program associated with the job request or job flow. The CLI polls the program until the CLI receives the program results. The results are written to stdout. For an example of using this option, see Capture Results from a Program or Command in SAS Viya Platform: Jobs and Flows.       I could not find an option to get the full log, just like we do when we execute using the batch submit-pgm command.   Think Security   Avoid hardcoding credentials in your code files. You can use an: An .authinfo file in conjunction with the pyviya tools. See Gerry's posts: Introducing the GEL pyviyatools. If you are using SAS Viya on Azure, use Azure Key Vault to store your secrets.   Conclusions   To execute in batch SAS Studio flows, from SAS Viya 2023.03 or later, consider using the sas-viya job requests CLI and the deploy, redeploy flow SAS Studio options.   Resources   For more information, see the following information: SAS Documentation: Deploying and Redeploying a Flow or Program as a Job. How to Run SAS Programs with Parameters in Batch in SAS Viya. Run SAS Programs in Batch in Viya 4. Introducing the GEL pyviyatools.   Thank you for your time reading this post. If you liked the post, give it a thumbs up. Please comment and tell us what you think about running flows in batch. If you wish to get more information, please write me an email.   ... View more

A Pipeline might be the door to the DevOps world. To enter this world, from SAS Viya, you need a key to the door. The key is a self-hosted agent for SAS Viya. A self-hosted agent can be a Docker container. The Docker container agent has several advantages compared to a Virtual Machine. Would you like to know what these advantages are? Or how to create a Docker agent for SAS Viya? Read the post to find out more. SAS Viya and Azure DevOps To integrate SAS Viya with Azure DevOps, you will need an agent.   To run a pipeline, your SAS code, and the pipeline definition (YAML) must be stored in the Git repository. On agents, the code from the Git repository is cloned and executed.   The agent interacts with a SAS Viya environment, through: SAS Viya Command Line Interface CLI SAS Viya REST APIs, which can be called with curl, Python, SAS or from any programming language. What are Agents? Azure DevOps Agents: Run the Azure Pipelines instructions. Can be Microsoft-hosted or self-hosted. Are hosted on Virtual Machines (VM), Docker containers or Virtual Machine Scale Sets. Can run on Linux, Windows, macOS VMs or in a Docker container. Initiate the communication with the Azure DevOps platform.   Docker Agents Advantages A Docker agent is really a Docker container that runs somewhere, for example on an Azure VM.   Inspired by Jan Kostrubiec's work with Docker agents for GitLab, I have been experimenting lately with Docker agents for Azure DevOps. I discovered a few advantages while using Docker containers: Small footprint. Fast to spin. You can have many Docker container agents running on the same Virtual Machine. That makes better use of the compute resources. You still must pay for parallel jobs running on self-hosted agents if your Git repository is private. Decoupling: if you bring your VM agent down, or you update it, you lose the execution agent. You can spin, update, test and destroy containers, while some are still active, listening for jobs. Create a Docker Agent in Ten Steps The example uses an Azure Linux VM to build your container image.   If you deployed SAS Viya on Azure, the jump-box-vm comes with the SAS Viya deployment. I will be using this machine which is a Linux Ubuntu 20.04.   If you deployed SAS Viya on-premises, pick a Linux machine with inbound and outbound internet connection.   Checklist: Establish communication between the VM and the SAS Viya Azure Kubernetes Service (AKS) cluster. Add an AKS Network Security Group (NSG) inbound rule for the jump-box-vm IP and the port 443. Get an Azure DevOps Personal Access Token (PAT). Set environment variables. Copy SAS Viya certificates (.pem). Download the sas-viya cli. Create a start agent script. Create a Dockerfile. Build the Docker container image. Run the Docker container. The agent is ready and listening for jobs.   Establish Communication The VM where you will run your container must be able to reach the Load Balancer IP of your SAS Viya deployment. Port 443 must be allowed. Create an Azure DevOps Personal Access Token (PAT) You will need to obtain a PAT, for the communication with the Azure DevOps organization. Set Environment Variables The scripts will require several environment variables: AZP_TOKEN: your Azure DevOps personal access token. MYUSER: optional prefix. AZP_AGENT_NAME: your Docker agent name. AZP_POOL: the agent pool where your agent will be registered. AZP_URL: your Azure DevOps Organisation URL. AZP_WORK: a work folder required for the agent. SUDO_USER: power user required to run the scripts. export AZP_TOKEN=Replace_with_the_PAT_you_obtained_previously export MYUSER=Replace_with_SAS_USER_ID # optional prefix # after replacement export AZP_AGENT_NAME=${MYUSER}-dockeragent export AZP_URL=https://dev.azure.com/My_DevOps_Org export AZP_POOL=$MYUSER-sas-viya-jump-vm export AZP_WORK=_work export SUDO_USER=jumpuser   Required Files To build the Docker image you will require a few files: Certificates: PEM file from your SAS Viya deployment. SAS Viya CLI TAR archive. Start script. Dockerfile. Create a folder:   mkdir -p ~/dockeragent && cd ~/dockeragent && ls Copy SAS Viya Certificates Upload the .pem file in the above folder. You can obtain the file by running this kubectl command:   export current_namespace=your_SAS_Viya_Namespace kubectl cp $(kubectl get pod -l app=sas-logon-app -o=jsonpath='{.items[0].metadata.name}'):security/trustedcerts.pem /home/jumpuser/.certs/${current_namespace}_trustedcerts.pem Download the SAS Viya CLI Download the .tgz file, the SAS Viya CLI from the SAS Downloads Page in the same folder. You can log in with your SAS profile.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Create a Start Agent Script Microsoft proposes a start script in Run a self-hosted agent in Docker. The script performs several actions: Uses the environment variables. Downloads packages and agent scripts. Creates an agent. Configures the agent. Starts the agent. Controls the stop of the agent. Create a new file. You might need to change the end of line conversion (Notepad++ example: Edit > EOL Conversion > Unix). Copy the contents:   #!/bin/bash # start.sh set -e if [ -z "$AZP_URL" ]; then echo 1>&2 "error: missing AZP_URL environment variable" exit 1 fi if [ -z "$AZP_TOKEN_FILE" ]; then if [ -z "$AZP_TOKEN" ]; then echo 1>&2 "error: missing AZP_TOKEN environment variable" exit 1 fi AZP_TOKEN_FILE=/azp/.token echo -n $AZP_TOKEN > "$AZP_TOKEN_FILE" fi unset AZP_TOKEN if [ -n "$AZP_WORK" ]; then mkdir -p "$AZP_WORK" fi export AGENT_ALLOW_RUNASROOT="1" cleanup() { if [ -e config.sh ]; then print_header "Cleanup. Removing Azure Pipelines agent..." # If the agent has some running jobs, the configuration removal process will fail. # So, give it some time to finish the job. while true; do ./config.sh remove --unattended --auth PAT --token $(cat "$AZP_TOKEN_FILE") && break echo "Retrying in 30 seconds..." sleep 30 done fi } print_header() { lightcyan='\033[1;36m' nocolor='\033[0m' echo -e "${lightcyan}$1${nocolor}" } # Let the agent ignore the token env variables export VSO_AGENT_IGNORE=AZP_TOKEN,AZP_TOKEN_FILE print_header "1. Determining matching Azure Pipelines agent..." AZP_AGENT_PACKAGES=$(curl -LsS \ -u user:$(cat "$AZP_TOKEN_FILE") \ -H 'Accept:application/json;' \ "$AZP_URL/_apis/distributedtask/packages/agent?platform=$TARGETARCH&top=1") AZP_AGENT_PACKAGE_LATEST_URL=$(echo "$AZP_AGENT_PACKAGES" | jq -r '.value[0].downloadUrl') if [ -z "$AZP_AGENT_PACKAGE_LATEST_URL" -o "$AZP_AGENT_PACKAGE_LATEST_URL" == "null" ]; then echo 1>&2 "error: could not determine a matching Azure Pipelines agent" echo 1>&2 "check that account '$AZP_URL' is correct and the token is valid for that account" exit 1 fi print_header "2. Downloading and extracting Azure Pipelines agent..." curl -LsS $AZP_AGENT_PACKAGE_LATEST_URL | tar -xz & wait $! source ./env.sh trap 'cleanup; exit 0' EXIT trap 'cleanup; exit 130' INT trap 'cleanup; exit 143' TERM print_header "3. Configuring Azure Pipelines agent..." ./config.sh --unattended \ --agent "${AZP_AGENT_NAME:-$(hostname)}" \ --url "$AZP_URL" \ --auth PAT \ --token $(cat "$AZP_TOKEN_FILE") \ --pool "${AZP_POOL:-Default}" \ --work "${AZP_WORK:-_work}" \ --replace \ --acceptTeeEula & wait $! print_header "4. Running Azure Pipelines agent..." trap 'cleanup; exit 0' EXIT trap 'cleanup; exit 130' INT trap 'cleanup; exit 143' TERM chmod +x ./run.sh # To be aware of TERM and INT signals call run.sh # Running it with the --once flag at the end will shut down the agent after the build is executed ./run.sh "$@" & wait $!   Save it as start.sh in the dockeragent folder. Create a Dockerfile The Dockerfile creates a Docker container (agent) image for a SAS Viya deployment: Pulls an image. Install packages. Copies the .pem file in a folder. Copies the .tgz file in a folder and extracts from it. Installs the sas-viya cli. Runs the start.sh script to configure the Azure DevOps agent.   # SAS Viya Docker Agent for Azure DevOps # v 0.2 # Starting Point: https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/docker?view=azure-devops#use-azure-kubernetes-service-cluster # SAS Viya specific elements # Tested with SAS Viya Stable and LTS 2023.03 # Added by Bogdan Teleuca 2023 June 23 # Contribution by Jan Kostrubiec FROM ubuntu:20.04 RUN DEBIAN_FRONTEND=noninteractive apt-get update RUN DEBIAN_FRONTEND=noninteractive apt-get upgrade -y RUN DEBIAN_FRONTEND=noninteractive apt-get install -y -qq --no-install-recommends \ apt-transport-https \ apt-utils \ ca-certificates \ curl \ git \ iputils-ping \ jq \ lsb-release \ software-properties-common \ python3-pip # SAS Viya specific elements \ jq # SAS Viya specific elements RUN curl -sL https://aka.ms/InstallAzureCLIDeb | bash # SAS Viya specific elements # Copy the SAS Viya TLS certificate WORKDIR /home/jumpuser/.certs COPY ./gelenv_trustedcerts.pem . # Copy and unpack the sas-viya cli tgz file - Release 2023.06 # Source: SAS Downloads Page https://support.sas.com/downloads/package.htm?pid=2512 WORKDIR /opt/sas/viya/home/bin ADD ./sas-viya-cli-1.21.10-linux-amd64.tgz . # Make the SAS-Viya CLI file executable and install all the plugins RUN chmod +x /opt/sas/viya/home/bin/sas-viya && /opt/sas/viya/home/bin/sas-viya plugins install --repo SAS all --force # # end specifically for a SAS Viya Agent # Can be 'linux-x64', 'linux-arm64', 'linux-arm', 'rhel.6-x64'. ENV TARGETARCH=linux-x64 WORKDIR /azp COPY ./start.sh . RUN chmod +x start.sh ENTRYPOINT [ "./start.sh" ] Build the Docker Container Image Before running the script, the environment variables must be correctly set.   # Check vars echo $AZP_AGENT_NAME echo $AZP_URL echo $AZP_POOL echo $AZP_WORK echo $SUDO_USER # Build the image docker build -t dockeragent:latest . # List the image docker image ls The build can take 3 to 5 minutes the first time, or even more.   When the build is complete, your Docker image is ready.     Run the Docker Container Finally, the docker command gets the latest version of the agent, configures it, and runs the agent. It registers the agent in the specified agent pool, in your Azure DevOps Organisation.   docker run -e AZP_URL=${AZP_URL} -e AZP_TOKEN=${AZP_TOKEN} -e AZP_AGENT_NAME=${AZP_AGENT_NAME} -e AZP_POOL=${AZP_POOL} dockeragent:latest       The Agent is Ready and Listening for Jobs     Discussion Thanks to my colleague Jan Kostrubiec for inspiring me to try Docker agents and helping me build a decent Dockerfile for SAS Viya. Conclusion SAS Viya and Azure DevOps can be easily integrated to realise several interesting use cases for our customers: code versioning, running SAS programs, jobs, SAS Studio flows, calling SAS Viya REST APIs, promoting code and content.   The self-hosted agent is key to using Azure DevOps with SAS Viya.   A Docker container can be a self-hosted agent. The containerised agent has several advantages compared to a Virtual Machine: fast to create and to destroy, small footprint, better use of compute resources, therefore optimizing costs and availability. Additional Resources Microsoft-hosted agents vs. self-hosted agents. Microsoft's Run a self-hosted agent in Docker. Virtual Machine Agents: Create a virtual machine on Azure. Read How to Create a SAS Viya Azure Pipelines Agent. Configure communication with the SAS Viya Azure Kubernetes Service cluster. Read How to Make Your Virtual Machine Talk with SAS Viya on Azure. SAS Viya and Azure DevOps Use Cases: A SAS Viya Model Release Pipeline with Azure DevOps A SAS Viya with Data Pipeline with Azure DevOps. A SAS Viya Decision Pipeline with Azure DevOps. SAS Viya CI/CD Pipelines with Azure DevOps.     Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about post content. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more