A more streamlined title would be: "Comparing GPT-4 Models on SWAT Code Generation for SAS Viya: A Study of 18 Prompts." How accurate is GPT-4 at generating SWAT code to perform light data management tasks in SAS Viya? With eighteen sets of prompts we tested two custom agents: one using the “base” GPT-4 model versus a second using a GPT-4 model grounded in documents highly relevant for SWAT code generation. Which one performed better? Are there any significant advantages using a RAG approach? Read the post and watch the videos to find out more.    Left: DALL-E generated image. Right: "Show me Morpheus" image. Source: imgur.   In the context of the movie "The Matrix," Morpheus responded to a claim of knowledge, when Neo exclaimed "I know Kung Fu", with "Show me," indicating a request for a demonstration of the skills in question.   If a GPT-4 custom agent would state "I know SWAT" (SAS Wrapper for Analytics Transfer) as a parallel to Neo's line, my response would follow along Morpheus' lines, inviting the custom agent to demonstrate the knowledge in a relevant situation.   And here came the response to that hypothetical "Show me".   GPT-4 Base vs GPT-4 with RAG   In our experiments, we compared side-by-side the SWAT code generation skills of two Azure OpenAI's GPT-4 models, version 1106-preview:   The 'Base' model refers to the standard deployment of GPT-4. The 'GPT-4 with RAG' variant, on the other hand, was enhanced with a Retrieval-Augmented Generation process and informed by a collection of nineteen documents. These documents comprised posts by Peter Styliadis, focusing on Getting Started with Python Integration to SAS® Viya® which we downloaded and converted to Word files to serve as a knowledge base for the model.   This approach builds upon the methods we detailed in our previous post, SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain: Behind the Scenes.   To understand the experiment, you might want to watch the following short video:     Summary of Results for Data Management Tasks Using GPT-4   After running eighteen distinct sets of prompts, we've compiled the outcomes of our experiments with two GPT-4 models: the standard 'Base' model and an enhanced version incorporating a Retrieval-Augmented Generation (RAG) technique. Here's how they performed:   Results  GPT-4 "Base" GPT-4 with RAG Successful 13 14 Partial Success (Different Results) 2 2 Unsuccessful 3 2 Total Tasks 18 18   Achieving a success rate of 15 or 16 out of 18 represents a strong performance. This suggests that both GPT-4 models are quite adept at handling light data management tasks, with the RAG-enhanced model showing a slight edge.   Nevertheless, we must approach these figures with a discerning eye. In the age of Business Intelligence (BI), it was not uncommon for five different dashboards to present five distinct sales figures. Language models, including the latest LLMs like GPT-4, haven't entirely resolved this issue. It's crucial to remember that while language models can significantly aid in data management tasks, the reliability of their outputs must be thoroughly vetted, particularly when those outputs inform critical business decisions.     Detailed Results for Data Management Tasks Using GPT-4   We prompted a series of data management tasks to evaluate the capabilities of two configurations of GPT-4, the 'Base' model and the enhanced 'GPT-4 with RAG' model. Our tasks varied in complexity:   Light Tasks: Listing caslibs, files, and tables. Medium Tasks: Generating table summaries, filters, top n results, group by operations, aggregations, and calculated columns. Heavy Tasks: Creating and saving tables, determining join columns for table joins, and promoting tables—some of the most challenging tasks for the model.   The models' performance should be viewed in light of their training data; the quality of their output is influenced by the data they've been exposed to during training. Here's how they fared:   ID Prompt GPT-4 "Base" GPT-4 with RAG Conclusion 1 List files Pass Pass Similar 2 List caslibs Pass Pass Similar 3 List in-memory tables Pass Pass Similar 4 Load a CSV from an URL to a promoted table Pass Pass Similar 5 Confirm the table has been loaded Pass Pass Similar 6 Column info Pass Pass (with an extra prompt). Base model slightly ahead. 7 Table summary statistics Pass Pass Similar 8 Describe a table Pass Pass Similar 9 Filter a table. Provide row counts. Pass Pass Different results. Trust issue. 10 New calculated column Pass Pass Similar 11 Top n Pass Pass Similar 12 Group by + aggregate. Pass Pass Different results. Trust issue. 13 Rename a column. Column info to confirm. Pass Pass Similar, RAG unaware of success. 14 Unique count for values in a column. Pass Pass RAG has better intent understanding. 15 Count the number of missing values from a table. Pass Pass RAG performs better, Base needs guidance. 16 Create a new promoted (global) table with a few lines of data. Fail Pass RAG handles promotion well. Base model fails to promote. 17 Join a table with the newly created table. Model must figure the key to join on. Fail Fail Task is challenging for both models. 18 Save a table and promote it. Filter an existing table, save as a promoted table. Fail Fail Both models struggle with table saving.   Ten Rounds of Prompts   This video presents a head-to-head challenge of ten rounds, where we prompt the model to tackle a series of data management tasks in SAS Viya. The tasks range from simpler ones, such as describing columns and summarizing tables, to more complex operations like creating calculated columns, sorting, and identifying top values. We also cover grouping with aggregations, renaming columns, performing unique counts, and saving tables after applying filters.   In most of the cases, both agents succeed at the given tasks, with the same results. Sometimes they both succeed but the results are different! Sometimes the RAG agent needs an extra “nudge” or further instructions. At other times, the RAG agent succeeds where the other failed or they both fail.   I won’t comment the full 28 minutes of the video, although I added a few explanations. Enjoy watching or scrolling through!     Excellence or Instead of Conclusions   Two custom agents were tested:   GPT-4 "Base" excels in 2 tasks: column info and rename a column followed by column info. GPT-4 with RAG shows superiority in 3 tasks: unique counts, missing values, promoting tables.   Overall, the results slightly favor the GPT-4 with RAG model, indicating a marginal edge in understanding and executing complex data management tasks.   Ultimately, the performance difference between the two models is relatively small. Considering the additional resources and time required to set up the RAG, one must weigh these against the need for precision.   For rapid outcomes where the highest accuracy is not critical, the 'Base' model is your go-to option. It provides quick results without the extra setup. The GPT-4 model 1106-preview version is a far cry compared with an earlier davinci-code-003 model I tested for SAS code generation.   However, if your priority is tailored accuracy and you're dealing with complex tasks where nuanced understanding is key, the 'GPT-4 with RAG' model is likely the better choice, despite the additional investment.   The study emphasizes the importance of verifying the output of language models, especially when informing critical business decisions.   I hope you found this article insightful. Please feel free to reach out with feedback or suggestions for enhancing the agent or taking its capabilities to the next level.     Acknowledgements   Thanks to Peter Styliadis for his great SWAT Series.   Additional Resources   How Retrieval Augmented Generation (RAG) Works? SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain: Behind the Scenes. SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain. 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 the approach. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Retrieval Augmented Generation (RAG) is an architecture that augments the capabilities of a Large Language Model (LLM) like ChatGPT by adding an information retrieval system that provides grounding data. Let’s start with some Large Language Model (LLM) generated poetry:   To RAG or not to RAG, that is the query, A question of depth, not at all imaginary. Shall we enhance our LLM with files galore, Or trust its vast knowledge, needing no more?   To RAG or not to RAG that is the question we would like to answer in this post.   RAG Data Sources   Azure OpenAI facilitates RAG by integrating pretrained models with your unique datasets. You can augment the prompts sent to the model by incorporating a data source of your choice. This can be done by uploading your files directly, utilizing existing blob storage, or connecting to a pre-existing AI Search index. Azure OpenAI supports a range of file types, including .md, .txt, .html, .pdf, as well as .docx and .pptx documents. It's important to note that if these files include graphics or images, the quality of the model's response will hinge on the effectiveness of text extraction from these visuals.   Index   For indexing, we utilize Azure AI Search, which is distinct from Azure OpenAI. This component is designed to construct a search index from your data.   Search   Azure OpenAI leverages Azure AI Search to enhance prompts by appending pertinent data snippets. By default, the system prompts the model to prioritize, but not exclusively rely on your data. However, this preference can be adjusted during setup, allowing the model to balance its pre-trained knowledge with the information from your data sources.   Retrieval Augmented Generation Overview Architecture Diagram   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   How to Implement Retrieval Augmented Generation (RAG) with Azure OpenAI   Watch the following video that explains how to add a data source to your model. The video compares the code generation question answers given by the "base" GPT-4 model versus the GPT-4 model grounded in your data.     As an intermediary conclusion, RAG could be an answer when the code you are trying to generate is "exotic" or proprietary or not covered by many public sources.   A Second RAG Example Loading a SAS Data Set into CAS – A Comparison   Suppose you are prompting GPT-4 ‘I'd like to perform an efficient client-side SAS data set load in memory, in CAS. Let's assume the data set is PRDSALE from SASHELP. I want to load it in CASUSER caslib.’   Without RAG: The code generation process would rely solely on pre-defined scripts and the programmer's knowledge to accomplish the task.   With RAG: The code generation is enhanced by dynamically pulling in relevant information from an index, potentially improving efficiency and accuracy of the generated code snippet for loading the data set into CAS.     CAS Code Generation with RAG   Let's explore how RAG streamlines the process of generating CAS code using a single markdown file from a GEL Data Management workshop that outlines efficient client-side in-memory data loading:   Once the file is uploaded to an Azure Storage Account and ingested by Azure AI Search to create an index, Azure OpenAI is ready to tap into this data source.     Here's what happens when you request SAS code to load a data set into CAS:   Azure OpenAI receives the user prompt, which in this case is a request to load the PRDSALE data set from SASHELP into the CASUSER caslib. It analyzes the prompt to grasp the desired content and intent, identifying the indexed file as highly relevant. The model then queries the search index using this intent. It incorporates the search results into the prompt, enriching it with both the system message and the user's original request. This enhanced prompt is sent to Azure OpenAI for processing. Azure OpenAI generates the SAS code, referencing the data where applicable, and returns it to the user.   And that's a brief rundown of RAG in action for CAS code generation.   Instead of a Conclusion   To RAG or not to RAG, ponder we must, For each choice we make bears its own kind of trust. Do we value the breadth, the external clout, Or the depth of the mind, what it’s all about?   Stay Tuned   In a next post, we will compare a SWAT custom LangChain agent powered by a GPT-4 model with a GPT-4 model + RAG on documents highly relevant for SWAT code generation. The comparison is based on eighteen prompts, asking the model to perform light, medium and increasingly difficult data management tasks in SAS Viya.   Additional Resources     Retrieval Augmented Generation (RAG) in Azure AI Search. SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain. SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain: Behind the Scenes. 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

The SAS Scripting Wrapper for Analytics Transfer (SWAT) custom agent is designed to be an interactive AI assistant. It streamlines SAS code generation and execution for users, regardless of their expertise in SAS, Python, or CAS actions. In this post, we'll look into the agent's code, outline the prerequisites, and guide you through replicating the agent in your own environment.   Previously   Check out the video in SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain for an insider's look at how this agent functions. I'll walk you through its operations, potential uses, and some of the intriguing behaviors that the custom agent displays.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Now it's time to reveal the code and walk you through the details.   Custom Agent Code   The core Python script, named sasgenexswat.py, establishes the SWAT LangChain custom agent that interfaces 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 - Azure OpenAI from langchain_openai import AzureChatOpenAI from openai import AzureOpenAI # Get Azure OpenAI environment variables from .env 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"] = "2024-05-01-preview" ## We'll use GPT-4 as deployed model llm = AzureChatOpenAI(deployment_name=azure_oai_model, temperature=0, max_tokens=2000) print("We'll be using the following LLM:\n", llm, "\n") # LLM Client from openai import AzureOpenAI client = AzureOpenAI( azure_endpoint = azure_oai_endpoint, api_key = azure_oai_key, api_version= "2024-05-01-preview" ) # Arguments import sys # Check if at least one argument is provided if len(sys.argv) > 1: # sys.argv[1] is the first parameter passed to the script sas_viya_url = sys.argv[1] print(f"""Passing prompts to Azure OpenAI. \nAzure OpenAI will generate the SWAT code and execute it on: {sas_viya_url}. \nAssuming you obtained an access token first by running get_token_2023.py.""") print('\nRetrieving the saved token from api/access_token.txt') with open("api/access_token.txt", "r", encoding="UTF-8") as f: token = f.read() else: print("No SAS Viya URL provided.") import swat # SWAT session # Define custom tools - SWAT GenEx from langchain.pydantic_v1 import BaseModel, Field from langchain.tools import BaseTool, StructuredTool, tool import re # code parsing from io import StringIO # exec output @tool def get_word_length(word: str) -> int: """Returns the length of a word.""" return len(word) @tool def generate_swat_code(prompt: str) -> str: """Returns the generated SWAT Python code, based on the user prompt. The code should be aimed at printing the execution output, as it would be used further. Args: prompt: The prompt which will be sent to Azure OpenAI. """ response = client.chat.completions.create( model=azure_oai_model, messages=[ {"role": "system", "content": "You are a SAS Python SWAT package assistant that helps people write SWAT code. Return only the Python SWAT code, ready to be executed. This is the code in between ```python and ```. Assume there is already a connection named 'conn' defined."}, # ... other system and user messages ... {"role": "user", "content": prompt}, ] ) sas_response = response.choices[0].message.content + '\n' # Strip code of characters pattern = re.compile(r"py\n(.*?)", re.DOTALL) match = pattern.search(sas_response) if match: swat_code = match.group(1).strip() print('\n',swat_code, '\n') else: swat_code = sas_response # Clean code block code_block = swat_code swat_code = code_block.replace("```python\n", "").replace("\n```", "") # Save the SWAT code to a file filename = "generated_swat_code.py" # Open the file first try: with open(filename, 'r', encoding='utf-8') as file: current_content = file.read() except FileNotFoundError: current_content = "" # Prepend the new SWAT code to the existing content new_content = '# Prompt -> ' + prompt + '\n' '# Generated code -> ' + '\n' + swat_code + '\n' + current_content with open(filename, 'w', encoding='utf-8') as file: file.write(new_content) print(f"Generated SWAT code has been saved to {filename}") return swat_code @tool def execute_swat_code(swat_code: str) -> str: """Useful when you need to execute generated Python SWAT code. Input to this tool is correct Python SWAT code, ready to be executed in a SAS Viya environment CAS session. Assume an already established SWAT connection, called 'conn'. Identify the results, summarize and provide a short explanation. If an error is returned, you may need to rewrite the SWAT code, using the generate_SWAT_code tool. 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. Args: swat_code: The SWAT Python code which will be submitted to a CAS session in SAS Viya. """ # Python code as a string stored in a variable print (f'\nI will submit the following code to {sas_viya_url}: \n',swat_code,'\n...End of code...\n') try: # Create SWAT connection using the url and a token obtained earlier port='443' conn_string= sas_viya_url + ':' + port + '/cas-shared-default-http' conn=swat.CAS(conn_string, password=token) # Create a dictionary to hold the execution context namespace = {'conn': conn} # Add 'conn' to the namespace old_stdout = sys.stdout old_stderr = sys.stderr redirected_output = sys.stdout = StringIO() redirected_error = sys.stderr = StringIO() try: # execute the SWAT code exec(swat_code, namespace) except Exception: # Optionally, handle the exception in some way here pass # Reset stdout and stderr to their original values sys.stdout = old_stdout sys.stderr = old_stderr # Get the captured output and errors output = redirected_output.getvalue() errors = redirected_error.getvalue() # Print the captured output and errors print("Captured Output:") print(output) print("Captured Errors:") print(errors) # Terminate SWAT connection conn.terminate() return output + errors except Exception as e: print(f'An error occurred: {e}') errors = e return errors # Describe the tools. The description is important for the LangChain chain. tools = [get_word_length, generate_swat_code, execute_swat_code] # LangChain Agent with Memory from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", "You are a world class AI assistant who helps people generate and execute SAS code. Interpret the results if asked.", ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) llm_with_tools = llm.bind_tools(tools) from langchain_core.messages import AIMessage, HumanMessage from langchain.agents.format_scratchpad.openai_tools import ( format_to_openai_tool_messages, ) from langchain.agents.output_parsers.openai_tools import OpenAIToolsAgentOutputParser chat_history = [] agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_tool_messages( x["intermediate_steps"] ), "chat_history": lambda x: x["chat_history"], } | prompt | llm_with_tools | OpenAIToolsAgentOutputParser() ) from langchain.agents import AgentExecutor agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True) # Main function def chat_with_open_ai(): # ... as before while True: print("SAS and Azure OpenAI are listening. Write 'Stop' or press Ctrl-Z to end the conversation.") try: # SWAT start session #conn = start_or_retrieve_swat_session(sas_viya_url) # Get text from the keyboard q = input("Enter your question: \n") if q == "Stop": print("Conversation ended.") #term = conn.terminate() #print('SWAT session ended successfully.', term) 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))   Custom Agent Code Explanation   The provided Python code integrates several components to create a custom agent that leverages Azure's OpenAI Large Language Model (LLM), specifically GPT-4, to generate and execute SAS code via the SAS Python SWAT (SAS Scripting Wrapper for Analytics Transfer) package. This agent is designed to facilitate interactions with SAS Viya, a cloud-enabled, in-memory analytics engine.   Here's a step-by-step breakdown of the code's functionality:   Imports and Environment Setup: The script starts by importing necessary Python modules. It loads environment variables from a .env file, which includes credentials and endpoints for Azure OpenAI services. The AzureChatOpenAI class from the langchain_openai package is instantiated with parameters set for the LLM, such as the model's deployment name and the maximum number of tokens to generate. Argument Handling: The script checks for command-line arguments, expecting at least one argument: the SAS Viya URL. If provided, it reads an access token from a local file to use for authentication with the SAS Viya server. Custom Tools Definition: Several functions are decorated with @tool from the langchain package, indicating that they are custom tools that can be used within the agent's execution flow. generate_swat_code tool sends the user prompt to Azure OpenAI, which returns the generated SWAT Python code. Letting an LLM to generate the code is the innovative aspect of the custom agent. @tool def generate_swat_code(prompt: str) -> str: """Returns the generated SWAT Python code, based on the user prompt. The code should be aimed at printing the execution output, as it would be used further. Args: prompt: The prompt which will be sent to Azure OpenAI. """ response = client.chat.completions.create( model=azure_oai_model, messages=[ {"role": "system", "content": """You are a SAS Python SWAT package assistant that helps people write SWAT code. Return only the Python SWAT code, ready to be executed. This is the code in between ```python and ```. Assume there is already a connection named 'conn' defined."}, # ... other system and user messages ... {"role": "user", "content": prompt}, ] ) The code is then cleaned and saved to a file. execute_swat_code retrieves the code from the generate_swat_code, establishes a SWAT connection, and then tries to run the generated SWAT code on a SAS Viya server using the SWAT package. It captures the output and errors, returning them to the agent. The SWAT connection is then terminated to liberate CAS compute resources on the SAS Viya server. @tool def execute_swat_code(swat_code: str) -> str: """Useful when you need to execute generated Python SWAT code. Input to this tool is correct Python SWAT code, ready to be executed in a SAS Viya environment CAS session. Assume an already established SWAT connection, called 'conn'. Identify the results, summarize and provide a short explanation. If an error is returned, you may need to rewrite the SWAT code, using the generate_SWAT_code tool. 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. Args: swat_code: The SWAT Python code which will be submitted to a CAS session in SAS Viya. """ try: # Create SWAT connection using the url and a token obtained earlier port='443' conn_string= sas_viya_url + ':' + port + '/cas-shared-default-http' conn=swat.CAS(conn_string, password=token) # Create a dictionary to hold the execution context namespace = {'conn': conn} # Add 'conn' to the namespace old_stdout = sys.stdout old_stderr = sys.stderr redirected_output = sys.stdout = StringIO() redirected_error = sys.stderr = StringIO() try: # execute the SWAT code exec(swat_code, namespace) except Exception: # Optionally, handle the exception in some way here pass # The SWAT code runs inside the exec(swat_code, namespace) function, after establishing a connection within the namespace. However, using exec means entrusting code execution to a custom agent without scrutiny, which isn't the most secure method for submitting SWAT code to SAS Viya. If you're aware of safer alternatives, I'm eager to hear them. LangChain Agent with Memory: A chat prompt template is defined using the LangChain package, which describes the behavior of an AI assistant capable of interpreting and acting on user input. The llm_with_tools object binds the custom tools to the LLM. from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", "You are a world class AI assistant who helps people generate and execute SAS code. Interpret the results if asked.", ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) llm_with_tools = llm.bind_tools(tools) The agent object is created by composing several functions and components, including the prompt, LLM, and an output parser. An AgentExecutor is instantiated, which handles the execution of the agent with the custom tools. Main Function (chat_with_open_ai): The main loop of the function listens for user input and uses the agent_executor to process the input, generating and executing SAS code accordingly. The chat history is updated with each interaction. The user can type "Stop" to end the conversation. The script is wrapped in a try-except block to handle any exceptions that occur during the chat loop. Execution: Finally, the chat_with_open_ai function is called to start the interactive session.       Now let’s look at what you need to build this custom agent.   SAS Viya Environment   To build this custom agent, you'll require a SAS Viya deployment. From your SAS Viya environment, you'll need to gather the following details: The SAS Viya URL A SAS username The corresponding SAS user's password   Additionally, ensure that CAS (Cloud Analytic Services) access is enabled for external connections to SAS Viya.   Azure Deployment If your SAS Viya is hosted on Azure, it's essential to configure an inbound security rule. This rule must include the IP address of the computer running Visual Studio Code and specify port 443. The target for this rule should be the public IP address of your SAS Viya's load balancer.   Azure OpenAI   To get started, you'll need to create a resource and deploy a model. If you're unfamiliar with the process, the Azure OpenAI QuickStart provides step-by-step guidance. To interact with Azure OpenAI, you require an endpoint, a key, and a deployment.   For this project, I selected the 1106-Preview GPT-4 model version, which was trained on data from December 2023. I've found that this version excels at generating SWAT code. Remember, choosing the right model is as crucial as the code that powers your application   Visual Studio Code   We will use Visual Studio Code with the Python extension installed to run the custom agent. If you’re a regular user of Visual Studio Code, great.   Python Requirements   Create a requirements.txt file that includes these Python packages: python-dotenv pandas urllib3 swat openai langchain langchain_openai langchain-core langchain-community   Azure OpenAI Configuration In the directory containing your main Python script, create a .env file to store your Azure OpenAI configuration parameters. The file should include the following lines: AZURE_OAI_ENDPOINT='https://myuser.openai.azure.com/' AZURE_OAI_KEY='key_here' #Azure Open AI key here AZURE_OAI_DEPLOYMENT='gpt-4' The sasgenexswat.py program is configured to read these environment variables from your .env file.   SWAT Setup Certificates Firstly, you'll need to obtain the SAS Viya certificates, which are in the .PEM file format, and download them to your local machine.   For detailed instructions, please refer to the provided documentation.   Get a SAS Viya Access Token   The sasgenexswat.py program is designed to read an access token from a file called api/access_token.txt.   For SWAT to establish a connection within the custom agent, you'll need an OAuth token. You have two options for obtaining this token:   Use any method you're comfortable with to acquire an access token and save it to api/access_token.txt. Follow the instructions in the post Discover Your Data with SAS Information Catalog APIs from Python – Access to run a program called get_token_2023.py. The program requires several variables.   Variables Once you have your certificates, open Visual Studio Code, launch a Bash terminal, and proceed with the setup. SASVIYAURL=my_sas_viya_url SASUSER=fill_in_here # SAS user SASPASS=pass_here # SAS user password CLIENT=fill_in_here # client id CLIENTSECRET=fill_in_here # client secret PEMPATH='fill_here' # for example PEMPATH='C:\Users\myuser\Downloads\trustedcerts.pem' export CAS_CLIENT_SSL_CA_LIST=${PEMPATH}   Get a Token Run a program called get_token_2023.py. You'll need to provide several environment variables. python get_token_2023.py $SASVIYAURL $CLIENT $CLIENTSECRET $SASUSER $SASPASS $PEMPATH   Install Python Packages   To install the Python packages listed in the requirements.txt file, execute the following command: pip install -U -r requirements.txt For additional context, if you're interested in the specific versions of LangChain, OpenAI, and SWAT packages used in the demo, you can retrieve them using these commands: pip freeze | grep lang pip freeze | grep openai pip freeze | grep swat python --version At the time of writing this post, the versions of the Python packages I am using are as follows: langchain==0.2.14 langchain-community==0.2.12 langchain-core==0.2.33 langchain-openai==0.1.22 langchain-text-splitters==0.2.2 langsmith==0.1.93 langchain-openai==0.1.22 openai==1.41.0 swat==1.14.0 Python 3.11.9   Run the Program   To execute the program, you'll need to provide your SAS Viya URL, which you've previously set as an environmental variable. Run the program by typing the following command: python sasgenexswat.py $SASVIYAURL   Prompts   Check out the video in SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain for prompts and responses.   Start asking questions such as:   List all files in the SAMPLES caslib List all CASLIBS. List all available in-memory tables in the 'Public' caslib. Load a CSV file from a URL into the Public caslib and promote the table. URL is  'https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/cars.csv'   List all available in-memory tables in the 'Public' caslib. Confirm CARS has been loaded. Print the columns of the CARS table. Request simple summary statistics for the CARS table from Public caslib. Describe the table CARS in the Public caslib. How many rows in the CARS CAS table from Public have the Make Toyota or Acura? From CARS, create a new calculated column NewCol2 = round(((MSRP - Invoice) / Invoice) * 100 and print three records to confirm the column creation. Find the top 10 MPG_City consumption values ordered ascending. Print the car make and model. Group the CAS table CARS from Public by Origin. Aggregate by column Invoice. Use means as a measure. Print the aggregation. Stop or CTRL+Z when you want to exit the program.   Conclusions   The SWAT code Generator-Executor (GenEx) custom agent approach is innovative, as it uses a large language model, GPT-4, to generate Python SWAT code based on user prompts. The custom agent is relying on just two main components: a SWAT code generator and a SWAT code executor. These tools work in tandem to run the generated SAS code within your chosen SAS Viya environment, managing both the output and any potential errors through the agent, which then crafts a final response.   SWAT code execution is the crucial step that makes large language model (LLM) code generation practical for SAS Viya users; it determines whether the code functions correctly in their environment.   Should an error arise, the agent is programmed to interpret the issue and attempt to regenerate the code, aiming for a successful execution. This can be a double-edged sword; while it's a source of strength, it can also be a weakness, as the custom agent may occasionally become stuck in a loop.   It's remarkable how just two hundred fifty lines of code can integrate enterprise analytics and data management platforms such as SAS Viya with AI platforms like Azure OpenAI. The linchpin that unites these diverse platforms remains open source.   I hope you found this article insightful. I encourage you to give it a try! Please feel free to reach out with feedback or suggestions for enhancing the agent or taking its capabilities to the next level.     Acknowledgements   Special thanks to Grace Liao from SAS Research & Development for inspiring me to try the approach.   Thanks to Peter Styliadis for his great SWAT Series.   Additional Resources   SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain. 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

The purpose of this custom agent is to create an interactive, AI-powered assistant that can simplify the process of generating and executing SAS code for users who may not be experts in SAS, Python or may not know anything about CAS and CAS actions. This can be particularly useful for data analysts and scientists who want to leverage the power of SAS Viya for complex data processing and analytics tasks without having to delve deeply into SWAT or SAS programming. The SWAT (SAS Scripting Wrapper for Analytics Transfer) package is a Python interface to SAS Cloud Analytic Services (CAS).   By leveraging the natural language understanding capabilities of Azure's OpenAI large language model (LLM), users can interact with SAS Viya in a conversational manner, asking questions or requesting actions to be taken on their data in SAS Viya. This custom agent serves as an interface that abstracts the complexity of coding by generating the necessary SWAT code from user prompts and executing it, thus allowing users to focus more on the analytical aspects of their work rather than the technical details of code syntax and structure.   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 Big Picture   This custom agent integrates multiple technologies, including SAS Viya, Azure OpenAI, and open-source Python packages like SWAT and LangChain.   The diagram depicts two circuits: the blue circuit and the red circuit.   In the blue circuit:   An LLM is at the core of LangChain custom agent. I am using GPT-4, from Azure OpenAI. The Generator tool inside the LangChain custom agent leverages a large language model (LLM), GPT-4, from Azure OpenAI, to generate SWAT code, basically Python, based on user prompts. I specifically chose a 1106-Preview model version. The 1106-Preview was trained on Dec 2023 data. I noticed this version "understands SAS better" and is naturally proficient at generating Python code, including SWAT. Choosing your model is as important as the programming behind the scenes. The Executor tool receives the generated SWAT code, creates a SWAT connection and is instructed to execute the generated Python code, the close the SWAT connection.   In the red circuit:   SWAT code execution logs and results are parsed and passed to the LangChain custom agent. At this point the LLM decides if the execution is successful. If so, it formulates a final result and prints it on the screen. If the execution results in errors or could not happen for any reason, a feedback loop starts. The Generator tool receives new instructions and new SWAT code is generated, and the whole process continues, like in the "blue circuit".   I like this approach, because of its simplicity. The custom agent requires only two custom tools, the generator and the executor.     Video   Watch the following video, where we explore the approach:   Possible Applications   The applications of the custom SWAT agent cover could enhance the user experience with SAS Viya. Here's a possible list of applications:   Automated Code Generation: This feature significantly streamlines the process of writing SWAT code. It's especially beneficial for users who may not be well-versed in SAS syntax, as it allows them to interact with SAS Viya through simple English prompts, making the analytics platform more user-friendly.   Power Under the Hood: This agent taps into the robust capabilities of the CAS engine for efficient data management tasks, such as loading, managing, and saving data from various sources. It can also utilize predefined connections to data sources known as caslibs, maximizing the utilization of existing infrastructure.   Accessibility and Inclusivity: By simplifying the complex aspects of programming, the agent lowers the barrier to entry for advanced analytics tools. This democratization of data science ensures that a broader audience can access and leverage powerful analytics capabilities.   Self-Correcting Feedback Loop: The inclusion of a self-correcting feedback loop where the agent reviews execution results and, in the case of errors, restarts the generation and execution cycle, introduces a layer of resilience and efficiency to the analytics process.   Interactive Analytics: The agent's conversational interface fosters an engaging and real-time interaction with the SAS Viya platform. Users can ask questions and receive immediate feedback, which enhances the analytical experience and accelerates decision-making.   Memory and Plasticity   The agent displays “plasticity”. The term "plasticity" is often associated with flexibility and adaptability. Here, it's used to describe the remarkable ability of an intelligent agent to adjust its actions and remember previous instructions.   Imagine you're working with an intelligent agent, and you've given it two tasks. The first task (Task A) is to load a table into memory, and the second task (Task B) is to query that table once it's loaded. If the agent encounters a problem with Task A, you can instruct it to try an alternative approach. Once Task A is successfully completed, the agent doesn't forget about Task B. Instead, it recalls that Task B was the next step and proceeds to perform it. This retention and sequential execution of tasks showcases the agent's memory capabilities.   This form of adaptability stems from the design of the LangChain agent. In this architecture, a language model (LLM) acts as a conduit between various tools and the custom behaviors you've programmed into those tools. In this scenario, I was clear about needing a code rewrite if the initial attempt failed. The agent's ability to handle this instruction is a proof to the flexibility built into its operation.   @tool def execute_swat_code(swat_code: str) -> str: """Useful when you need to execute generated Python SWAT code. Input to this tool is correct Python SWAT code, ready to be executed in a SAS Viya environment CAS session. Assume an already established SWAT connection, called 'conn'. Identify the results, summarize and provide a short explanation. If an error is returned, you may need to rewrite the SWAT code, using the generate_SWAT_code tool. 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. Args: swat_code: The SWAT Python code which will be submitted to a CAS session in SAS Viya. """     Conclusions   By integrating the intuitive language understanding of Azure OpenAI's GPT-4 with the practical functionality of SWAT and LangChain, this agent removes the barrier of complex code, allowing users to command their simple data management tasks through simple conversation. The agent displays some "plasticity" – its ability to remember and adapt to tasks – when given a memory of previous conversations. This innovative approach may democratize data processing, empowering a broader range of professionals to make data-driven decisions swiftly and confidently.   I hope you enjoyed this article. And please contact me if you have any feedback or any ideas on how to improve the agent or take it one level further.   What's Next   Discover the inner workings of SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain: Behind the Scenes. I'll unveil the custom agent Python program and detail the prerequisites for running it in your own environment.   In future posts, I will:   Add RAG to the custom agent. RAG or Retrieval-Augmented Generation, is a technique used in natural language processing (NLP) that combines the power of language models with the ability to retrieve information from relevant documents. Specifically for SWAT, I would be using Peter Styliadis' series and see if we achieve better results at SWAT code generation. Compare the "vanilla" custom agent code generation ability with the one powered by a RAG and discuss the results.   Acknowledgements   A big thank you to SAS Project Anemoi 's members, selflessly sharing their GenAI experiences.   Additional Resources   SWAT Code Generation and Execution in SAS Viya with Azure OpenAI and LangChain: Behind the Scenes. 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. SASPY: Submit workloads to SAS Viya from Python. LangChain Custom Agent. 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

Let's dive into crafting GitLab CI/CD pipelines tailored for SAS Viya. First, you established a GitLab Kubernetes runner. Next, you crafted a bespoke Docker image suited for your SAS Viya ecosystem and uploaded it to a secure, private GitLab container registry. Read this post to understand what a pipeline for SAS Viya looks like, what the different components are and how to put them all together.   Post Series   In this post series you will learn how to:   Register the GitLab Kubernetes Runner. Create Your Own Docker Image for SAS Viya. Use GitLab Pipelines to Execute Commands in SAS Viya, using the Docker image you built as the base for your executor – the current post   Video   Take a look at the following video, in which we demonstrate a GitLab CI/CD pipeline that runs a SAS program in SAS Viya.     GitLab CI/CD Pipeline   The pipeline is designed to submit a SAS program, which is stored in your Git repository, to SAS Viya for execution. This program's task is to load a table into memory within a caslib.   Begin by creating a .gitlab-ci.yml file in your repository's root directory:   default: image: $CI_REGISTRY/$GITLAB_USER_LOGIN/sasgitlabdevops/jass/gitlab-runner-sas:0.07 run-sas-program: stage: build script: - echo "Hello, $GITLAB_USER_LOGIN!" - echo "CI Registry $CI_REGISTRY" # test container folders - ls -la - ls -la /usr/local/bin # SAS Viya CLI - sas-viya plugins list # TLS Certificate location - export SSL_CERT_FILE=/usr/local/bin/gelenv_trustedcerts.pem - echo ${SSL_CERT_FILE} # SAS Viya CLI profile - sas-viya --profile dev profile set-endpoint $dev_endpoint - sas-viya --profile dev profile toggle-color on - sas-viya --profile dev profile set-output fulljson - sas-viya --profile dev profile show - sas-viya --profile dev auth login -user $dev_user -password $dev_passwd # SAS Viya CLI - echo Run a SAS program stored in Git - sas-viya --profile dev batch jobs submit-pgm --pgm-path 1.sas --context default --watch-output --wait-log-list --results-dir ~/   Explanation   Image   default: image: $CI_REGISTRY/$GITLAB_USER_LOGIN/sasgitlabdevops/jass/gitlab-runner-sas:0.07   The configuration snippet you've provided is designed to pull a specific Docker image from your GitLab Container Registry for use in your CI/CD pipeline. This Docker image is the one you previously constructed for your SAS Viya environment.   Here's a breakdown of the image directive in your .gitlab-ci.yml file:   `default:` sets the default configuration for all jobs in the pipeline. `image:` specifies the Docker image to use. $CI_REGISTRY and $GITLAB_USER_LOGIN are placeholders for GitLab's predefined variables; $CI_REGISTRY will become the URL of the GitLab registry, typically registry.gitlab.com. $GITLAB_USER_LOGIN will be replaced with your GitLab username.   The complete image name, including the tag, will be resolved using these variables, ensuring that the correct image is used for the pipeline.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   To learn more about building the Docker image, refer to the associated post.   Executor Pod   The pipeline runs. The Kubernetes runner initiates the execution. The runner creates a temporary Kubernetes executor pod from the pulled Docker image.       Stage, Job and Script   The basic bricks in a GitLab CI/CD pipeline are the stage, which contains jobs. The job contains scripts.   run-sas-program: stage: build script: - echo "Hello, $GITLAB_USER_LOGIN!"   Where:   run-sas-program job runs inside the build stage: script: runs a series of shell scripts: printing variables, listing folders, etc.   SAS Viya CLI commands   List the plugins. Initialize a variable needed by the SAS Viya CLI: the location of the SAS Viya TLS certificates. Recall from step two, we ‘baked in’ the certificate in the Docker image. Create a SAS Viya profile using variables declared when we setup the GitLab runner. Authenticate against SAS Viya (get an access token). Submit a SAS program in batch.   # SAS Viya CLI - sas-viya plugins list # TLS Certificate location - export SSL_CERT_FILE=/usr/local/bin/gelenv_trustedcerts.pem - echo ${SSL_CERT_FILE} # SAS Viya CLI profile - sas-viya --profile dev profile set-endpoint $dev_endpoint - sas-viya --profile dev profile toggle-color on - sas-viya --profile dev profile set-output fulljson - sas-viya --profile dev profile show - sas-viya --profile dev auth login -user $dev_user -password $dev_passwd - sas-viya --profile dev batch jobs submit-pgm --pgm-path 1.sas --context default --watch-output --wait-log-list --results-dir ~/   Notice the variables:   $dev_endpoint is the URL of our SAS Viya environment. $dev_user is the SAS user executing the pipeline. $dev_passwd is the credential for SAS Viya CLI authentication.   These are Project variables, defined under Settings > CI/CD > Variables in GitLab.     From a security perspective you should mask sensitive variables, so that they can’t be printed by accident in a log.     Finally, by using the SAS Viya CLI, you can submit the SAS program to be executed in SAS Viya.    - sas-viya --profile dev batch jobs submit-pgm --pgm-path 1.sas --context default --watch-output --wait-log-list --results-dir ~/     Ultimately, you should see the table successfully loaded in SAS Viya.   Conclusion   In conclusion, this post has walked you through setting up a GitLab CI/CD pipeline designed for SAS Viya, utilizing the Kubernetes runner established in the first step and a custom Docker image crafted in the second step. Looking ahead, we will delve into additional SAS Viya pipelines and various runner options. Thank you for following along!   Acknowledgements   Many thanks to Jan Kostrubiec @JanKostrubiec , Katarzyna Zuk @KatarzynaŻuk , Lars Arne Skår @larsarne , Neil Griffin @Neil-Griffin , from whom I learned the most about DevOps with SAS Viya and GitLab.   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

  Learn how to create your own self-hosted Azure Pipelines agent. The agent is a Linux Virtual Machine (VM), hosted in Microsoft Azure. This agent performs the build tasks defined in the Azure Pipelines YAML. The YAML can instruct the agent to run a SAS program or SAS Studio flow in batch, import a SAS package, publish a SAS model or a decision to a destination such as Azure or Git.   Overall Picture   The self-hosted agent is the key piece for SAS Viya to work with Azure DevOps. The steps are: Create a virtual machine that acts as a self-hosted agent (this post). Read How to Make Your Virtual Machine Talk with SAS Viya on Azure to learn how to configure communication with the SAS Viya cluster: define Network Security Groups rules, copy certificates, etc. Create an Azure DevOps self-hosted agent. Install software and packages on the self-hosted agent: SAS Viya Command Line Interface CLI, utilities such as pyViyaTools, Python, etc.   Azure Virtual Network Topology   The self-hosted agent is the virtual machine highlighted in the following Azure virtual network VNET topology. For security and many practical reasons, you will have to create not only the virtual machine, but also many other Azure resources.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   You can write Azure CLI directly in the Cloud Shell or from a machine with cloud access. The commands will create Azure resources.   Create Azure Resources   To realize the above topology, I recommend you follow these steps. As you can see, creating the agent Virtual Machine is actually the last step in this process. SSH Keys If You Have the Keys If You Do Not Have the Keys Initialize Variables Create a Resource Group Create a Virtual Network and Subnet Create a Public IP Address Create a Network Security Group Create Network Security Group Rules Create a Virtual Network Interface Card Create an Availability Set Create a Virtual Machine Test SSH to Your VM SSH Keys   To manage the virtual machine you will create, you will need to connect to it using Secure Shell (SSH). To connect using SSH, you will need a set of SSH keys.   If You Have the Keys   First, check you have SSH keys that you can use:   ls -la ~/.ssh # create the private_key variable to match the key name export private_key=~/.ssh/gelazuredm-aks-key   In my case, the key pair was previously generated. It contains: The private key gelazuredm-aks-key The public key gelazuredm-aks-key.pub If You Do Not Have the Keys   If you do not have the keys, you can generate them using the following statement:   cd ~/.ssh ssh-keygen -m PEM -t rsa -b 4096   Name the new keys. You could use a passphrase, which is more secure.   # adapt the private_key variable to match the key name # export private_key=~/.ssh/gelazuredm-aks-key2     Initialize Variables   For the resources you are about to create, it is best to adopt a naming convention. In this example, the resources will be named using the particle $MYUSER:   Create a Resource Group   An Azure resource group is a logical container into which Azure resources are deployed and managed. A resource group must be created before a virtual machine and supporting virtual network resources:   # adapt the private_key variable to match the key name az group create --name $RG --location $LOCATION   Create a Virtual Network and Subnet   Create a virtual network in Azure and a subnet into which you can create your VM. Use az network vnet to create a virtual network named $VNET with the Classless Inter-Domain Routing (CIDR) 10.2.0.0/28 (equivalent to 16 IPs). You also add a subnet named $SUBNET with the CIDR 10.2.0.0/29 (8 IPS). This is the minimum and we really do not need more for our purpose:   az network vnet create \ --resource-group $RG \ --name $VNET \ --address-prefix 10.2.0.0/28 \ --subnet-name $SUBNET \ --subnet-prefix 10.2.0.0/29   Create a Public IP Address   Now let's create a public IP address with az network public-ip create . This public IP address enables you to connect to your VMs from the Internet. Because the default address is static, create with the --allocation-method Static parameter.   az network public-ip create \ --resource-group $RG \ --name $IP \ --allocation-method Static \ --sku Basic   Create a Network Security Group   To control the flow of traffic in and out of your VMs, you apply a network security group to a virtual Network Interface Card (NIC) or subnet. The following example uses az network nsg create to create a network security group named $NSG:   az network nsg create \ --resource-group $RG \ --name $NSG Create Network Security Group Rules   You define rules that allow or deny specific traffic. To allow inbound connections on port 22 (to enable SSH access), create an inbound rule with az network nsg rule create . The following example creates a rule named AllowSSH:   az network nsg rule create \ --resource-group $RG \ --nsg-name $NSG \ --name ${MYUSER}AllowSSH \ --protocol tcp \ --priority 1000 \ --source-address-prefixes 148.172.0.0/16 \ --destination-port-range 22 \ --access allow Note: 148.172.0.0/16 is an IP where employees can connect to the internal network using VPN. Therefore, the rule will allow only organization employees who are connected with corporate VPN to SSH the VM.     Create a Virtual Network Interface Card   Virtual network interface cards (NICs) are programmatically available because you can apply rules to their use. Depending on the VM size, you can attach multiple virtual NICs to a VM. In the following az network nic create command, you create a NIC named $NIC and associate it with your network security group. The public IP address $IP is also associated with the virtual NIC.   az network nic create \ --resource-group $RG \ --name $NIC \ --vnet-name $VNET \ --subnet $SUBNET \ --public-ip-address $IP \ --network-security-group $NSG     Create an Availability Set   Availability sets help spread your VMs across fault domains and update domains. Even though you only create one VM right now, it's best practice to use availability sets to make it easier to expand in the future. Create an availability set for your VM with az vm availability-set create . The following example creates an availability set named $AS:   az vm availability-set create \ --resource-group $RG \ --name $AS   Create a Virtual Machine   You've created the network resources to support Internet-accessible VMs. Now create a VM and secure it with an existing SSH key. In this example, let's create an Ubuntu VM based on the most recent LTS. You can find additional images with az vm image list .   Specify an SSH key to use for authentication. If you do not have an SSH public key pair, you can create them or use the --generate-ssh-keys parameter to create them for you. If you already have a key pair, this parameter uses existing keys in your ~/.ssh folder. Create the VM by bringing all the resources and information together with the az vm create command .   The following example creates a VM named $VM:   az vm create \ --resource-group $RG \ --name $VM \ --location $LOCATION \ --availability-set $AS \ --nics $NIC \ --image UbuntuLTS \ --admin-username $VM_USER \ --ssh-key-value ${private_key}.pub \ --size Standard_B2s     SSH to Your VM   The infrastructure was created. Test the connection, SSH to your VM with the public IP address created:   # SSH connect to your VM JUMPBOXIP=$(az vm list-ip-addresses -g $RG -n $VM --query "[].virtualMachine.network.publicIpAddresses[0].ipAddress" -o tsv) echo "${MYUSER} Jump Box Vm's Public IP: $JUMPBOXIP" cd ~/.ssh ssh -i $private_key jumpuser@$JUMPBOXIP   Conclusion   You created a Virtual Machine that will be configured as a self-hosted agent. Read the next posts to learn how to: Configure communication with the SAS Viya cluster (Network Security Groups rules, certificates). Install software on the self-hosted agent (SAS Viya Command Line Interface CLI, utilities such as pyViyaTools, Python, etc.).   Discussion   There are alternatives to the proposed Azure Virtual Network Topology. The SAS Viya deployment on Azure comes with a pre-deployed virtual machine. You can use it to configure it as a self-hosted Azure Pipelines agent too, without creating a new one.     Read the Next Post in the Series Want to know how to configure this VM to communicate with SAS Viya? Read How to Make Your Virtual Machine Talk with SAS Viya on Azure.   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

The second step in this journey involves creating a Docker image that's customized for your SAS Viya environment. After crafting it, the next move is to push this tailor-made Docker image to a GitLab container registry. But what exactly does the Docker image do? How does it integrate with a Kubernetes executor? And what are the benefits of hosting this image in a private GitLab container registry? Explore the post to uncover these answers.   Post Series   In this post series you will learn how to:   Register the GitLab Kubernetes Runner. Create Your Own Docker Image for SAS Viya – the current post. Use GitLab Pipelines to Execute Commands in SAS Viya, using the Docker image you built as the base for your executor.   Summary   For secure execution of SAS Viya jobs using a Kubernetes executor, a Docker image containing essential SAS Viya elements is necessary. This image, which includes the SAS Viya Command Line Interface (CLI) and environment-specific TLS certificates, is pulled by Kubernetes to create an executor pod. The CLI enables task execution and log retrieval within the SAS Viya environment, making the custom Docker image a critical component for operations.   Video   Take a look at the following video, in which we demonstrate the steps to build the Docker image.     Steps   Access Token To authenticate with your private GitLab container registry, you must obtain an access token from your GitLab project. Navigate to 'Settings' and then 'Access Token' to create one. Ensure the token has 'write_repository' permissions for the necessary access or a role that has these permissions (e.g., Developer).   Container Registry Authentication To push a Docker image to a private registry, authentication is required. On a system with Docker installed, use your GitLab credentials to log in to the GitLab registry. For the password, use the access token you created in GitLab.   Docker Image Here is a streamlined version of the instructions:   SAS Viya TLS certificates Create a folder to house the necessary files. Obtain the certificates using a kubectl command.   mkdir -p docker_gitlab cd docker_gitlab mkdir -p files # Obtain the Truststore Certificate File from Your Cluster kubectl -n name-of-namespace cp $(kubectl -n name-of-namespace get pod | grep "sas-logon-app" | awk -F" " '{print $1}'):security/trustedcerts.pem files/gelenv_trustedcerts.pem # Copy the certificate in /files ls -la ls -la files     The SAS Viya CLI file For Linux, download the TGZ file from the SAS Downloads Page in the docker_gitlab/files folder.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Dockerfile Within the docker_gitlab folder, craft a Dockerfile.   tee Dockerfile> /dev/null << EOF FROM centos:8 MAINTAINER YourName “your email” RUN sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-* RUN sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-* RUN dnf update -y && dnf upgrade -y \ && dnf install epel-release -y \ && dnf install jq -y \ && dnf install -y nfs-utils \ && dnf clean all \ && rm -rf /var/cache/yum ADD files/sas-viya-cli-1.21.30-linux-amd64.tgz /usr/local/bin WORKDIR /usr/local/bin COPY ./gelenv_trustedcerts.pem . RUN sas-viya plugins install --repo SAS audit \ && sas-viya plugins install --repo SAS authorization \ && sas-viya plugins install --repo SAS batch \ # && sas-viya plugins install --repo SAS cas \ # && sas-viya plugins install --repo SAS compute \ && sas-viya plugins install --repo SAS configuration \ # && sas-viya plugins install --repo SAS dcmtransfer \ # && sas-viya plugins install --repo SAS decisiongitdeploy \ # && sas-viya plugins install --repo SAS devices \ # && sas-viya plugins install --repo SAS folders \ # && sas-viya plugins install --repo SAS fonts \ # && sas-viya plugins install --repo SAS identities \ && sas-viya plugins install --repo SAS job \ # && sas-viya plugins install --repo SAS launcher \ # && sas-viya plugins install --repo SAS licenses \ # && sas-viya plugins install --repo SAS mip-migration \ # && sas-viya plugins install --repo SAS models \ # && sas-viya plugins install --repo SAS notifications \ # && sas-viya plugins install --repo SAS oauth \ # && sas-viya plugins install --repo SAS qkbs \ # && sas-viya plugins install --repo SAS reports \ # && sas-viya plugins install --repo SAS rtdmobjectmigration \ # && sas-viya plugins install --repo SAS scoreexecution \ # && sas-viya plugins install --repo SAS sid-functions \ && sas-viya plugins install --repo SAS transfer \ # && sas-viya plugins install --repo SAS workload-orchestrator \ && sas-viya plugins list CMD ["/bin/bash"] EOF   Building a Docker image requires specific files, including the SAS Viya TLS certificates and the SAS Viya CLI file, which can be obtained from the support.sas.com website. Let's examine the Dockerfile steps:   The process begins by pulling the base image. While we use a CentOS image, alternative modern base images like RedHat's ubi8, which serves as the foundation for many SAS container images, can also be used. The next step updates and upgrades the existing packages on the system, followed by the installation of additional repositories, tools, and utilities. After setting up the environment, we incorporate the core SAS Viya components by adding the TLS certificates, and then we proceed to install the SAS Viya CLI. We decided to install only a few plugins, to keep the image size light.   Docker Build Proceed to build the Docker image. Assign a tag to the newly created image. Push the tagged image to your private GitLab container registry.   GITLAB_REGISTRY=registry.gitlab.com/${YOUR_GITLAB_USER}/${YOUR_GITLAB_PROJECT} TAG=0.07 # Build from Dockerfile docker build -t ${GITLAB_REGISTRY}/jass/gitlab-runner-sas:${TAG} . # List images docker image ls # Push to GitLab container registry docker push ${GITLAB_REGISTRY}/jass/gitlab-runner-sas:${TAG}   Discussion   Why a custom image? The question of using a custom image versus a pre-existing SAS Viya CLI Docker image is valid. While it's true that there is a SAS Viya CLI Docker image available, it's primarily suited for use with a shell executor, not directly with a Kubernetes executor.   For Kubernetes executors, which operate within a pod, having the TLS certificates accessible is essential, as well as the SAS Viya CLI image. There may be alternative methods to achieve this, but they would require further investigation.   Why a private GitLab container registry? As you have the TLS certificate “baked in” the image, you must keep that image private. You can’t put it on a public registry as you would expose your SAS Viya certificates.   That's exactly the reason for using a private GitLab container registry, a decision rooted in security.   Conclusion   In this post, we've guided you through the steps to create a Docker image customized for your SAS Viya environment and demonstrated how to push the custom Docker image to a GitLab container registry.   Moving forward, we'll explore how to securely run SAS Viya jobs using GitLab CI/CD pipelines.   Thank you for reading!   Acknowledgements Many thanks to Jan Kostrubiec @JanKostrubiec, Katarzyna Zuk @katarzynazuk, Lars Arne Skår @larsarne, and Neil Griffin @Neil-Griffin, from whom I learned the most about DevOps with SAS Viya and GitLab.   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

The purpose of this post series is to guide you to configure GitLab CI/CD pipelines for effective operation with SAS Viya. The first step on this journey is to register a GitLab runner and define an executor. What is the role of the GitLab runner, what is an executor and why do you need one for triggering SAS Viya workloads? Read the post to find out more.     Post Series   In this post series you will learn how to:   Register the GitLab Kubernetes Runner – the current post. Create Your Own Docker Image for SAS Viya. Use GitLab Pipelines to Execute Commands in SAS Viya, using the Docker image you built as the base for your executor.   Before diving in the details, let’s answer the following question:     What's the Objective Here?   Implementing GitLab CI/CD pipelines brings numerous benefits to SAS Viya operations. These include automating processes, code versioning, fostering collaboration in development, facilitating content transfer across various environments, storing as well as archiving artifacts.   GitLab CI/CD pipelines empower you to:   Automate segments of a business process. Execute SAS and Python programs from a version-controlled GitLab repository. Import SAS packages from a JSON file within the same repository. Initiate a SAS Studio flow or execute a SAS job. Export SAS packages into JSON files. And accomplish much more.     Video   Take a look at the following video, in which a GitLab Kubernetes runner is registered and tested.       GitLab Concepts   After the video, let's familiarize ourselves with some GitLab concepts:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     GitLab Job: This is the smallest component of a pipeline, which contains one or more commands that need to be executed, such as to run a SAS program in SAS Viya. The jobs and the commands are defined inside a file .gitlab-ci.yml.   GitLab Runner: This is an agent installed on a server different from the GitLab server. The GitLab runner receives instructions from the GitLab server on which jobs to run. Each runner must be registered with the GitLab server.   Executor: Each runner requires at least one executor. An executor is the environment where the job will be executed.   Thus, the crucial distinction is: The GitLab Kubernetes runner acts as the agent that oversees and delegates the tasks, whereas the Kubernetes executor is the environment where these tasks are actually carried out.   GitLab offers various executors that you can use depending on your needs. These include Shell, Kubernetes, Docker, SSH, VirtualBox, Parallels, and Docker Machine.     Executors   In this post we will focus on a Kubernetes executor. Why Kubernetes?   We utilize a Kubernetes runner and executor since SAS Viya is deployed within a Kubernetes cluster. The Kubernetes executor pod is temporary, existing only for the duration of the job execution. Utilizing a Kubernetes executor is advantageous in this context. The runner spawns an executor pod using a Docker image, which can be sourced from a private container registry linked to your GitLab project or a public one like Docker Hub.     Setting Up a GitLab Runner   There are five simple steps to create the GitLab runner:   Create the runner. Store the runner authentication token. Deploy and start the runner. Verify the runner registration. Run a test job.     1. Create the runner   In your GitLab repository, perform the following steps:   Navigate to Settings -> CI/CD and expand the 'General pipelines' section. Under 'Git strategy', set the 'Git shallow clone' field to 0. This has been found by my SAS colleagues to optimally support pipeline functioning. Expand the 'Runners' section, switch to the 'Instance runners' tab and deactivate instance runners. Generate a new project runner. Select 'Linux', add a tag, enable 'Run untagged jobs', designate the runner name, and set the 'Maximum job timeout' to a value superior to 600. Finally, create the Runner. Enabling 'Run untagged jobs' permits the runner to execute all pipelines, not just those specifying the tag. With the above option disabled, if your aim is to execute certain pipelines on the runner, it's necessary to incorporate the tag within the pipeline code.     2. Store the runner authentication token   Copy the value of the runner authentication token and save it somewhere safe.   GITLABTOKEN=glrt-....     3. Deploy and start the runner   For clear workload separation, the runner will be deployed in a fresh Kubernetes namespace. Executors will operate as pods within this specifically assigned workspace.   kubectl create namespace gitlab   A values.yaml file allows you to customize some of the runner pod settings, such as pods resources, number of replicas. You can find the full list of variables values.yaml.   cd ~ tee values.yaml > /dev/null << EOF ## Configure resource requests and limits for the runner pods resources: limits: memory: 1024Mi cpu: 750m requests: memory: 512Mi cpu: 500m ## How many runner pods to launch replicas: 2 EOF   The official way of deploying a GitLab runner instance into your Kubernetes cluster is by using the gitlab-runner Helm chart. This chart configures a GitLab runner to run using the Kubernetes executor. For each new job it receives from GitLab CI/CD, it provisions a new pod within the specified namespace to run it.   GITLABTOKEN=glrt-.... helm repo add gitlab https://charts.gitlab.io helm install \ -f values.yaml \ --namespace gitlab gitlab-sasviya-008 \ --set gitlabUrl=https://gitlab.com/,runnerRegistrationToken=$GITLABTOKEN \ --set rbac.create=true \ --set runners.privileged=true \ --set checkInterval=5 \ --set nodeSelector."kubernetes\.io/role"=agent \ gitlab/gitlab-runner   Explanation of the options:   'helm repo' - Adds the GitLab Chart to Helm. 'helm install' - Deploys the runner. '-f values.yaml' - Includes the settings defined in your custom 'values.yaml' file. '--namespace gitlab' - Specifies the Kubernetes namespace. 'gitlab-sasviya-008' - Links with the runner name we registered in the GitLab interface. 'gitlabUrl' - Points to the full URL where your GitLab project is hosted. In some organizations, GitLab can be deployed on a company subdomain. 'runnerRegistrationToken' - Indicates the token generated when you registered the runner. The token allows the runner to authenticate in GitLab. '--set rbac.create' - If your cluster has RBAC enabled, you can opt to have the chart create the service account for you. '--set runners.privileged' - Allows the GitLab Runner to run using privileged containers. This might be necessary if you need to use the Docker executable within your GitLab CI/CD jobs. '--set checkInterval' - Defines in seconds how often to check GitLab for new builds. '--set nodeSelector."kubernetes.io/role"=agent' - Labels the GitLab Runner pods.   For more general information about Helm, see Helm basics.     4. Verify the runner registration   To verify the registered runners, navigate to Settings -> CI/CD and click on 'Expand' in the runners section.     5. Run a test job   Create a new file .gitlab-ci.yml in your GitLab repository, and paste the following inside:   show-hello: script: - echo "hello world"   Commit the changes with a comment. Go to Build -> Pipelines and check the status.     Conclusion   In this post, we've walked through the process of setting up a GitLab runner for your GitLab repository in a Kubernetes cluster. This is an essential first step towards seamless CI/CD operations with SAS Viya.   In the following steps, you'll construct a Docker image that your Kubernetes executor will use to send commands to SAS Viya and write a few SAS Viya CI/CD pipelines.   Thank you for your time!     Acknowledgements   Many thanks to @JanKostrubiec, @katarzynazuk, @larsarne, and @Neil-Griffin from whom I learned the most about DevOps with SAS Viya and GitLab.     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

The business glossary is not just an add-on, but an indispensable tool in any data catalog. Starting from SAS Viya release LTS 2024.03 (May 2024), the SAS Information Catalog has introduced a brand new glossary. It facilitates a common understanding of data elements in an organization by defining terms in a clear, accessible language. The glossary eliminates ambiguity in business terminology, fostering consistent communication throughout the organization. To discover a summary of the key functionalities, please read more.   Why is the Glossary Essential?   Consider non-cataloged data as a chaotic pile of unsorted books. Would you want to be the person desperately searching for a specific piece of information in this random heap of data?   AI-generated image: ChatGPT 4o.   In contrast, cataloging data is akin to neatly organizing these books onto shelves. Wouldn't you rather be the individual who can effortlessly scan through a well-arranged library to find what they need?   AI-generated image: ChatGPT 4o.   This is where the glossary steps in. Acting as a well-structured library, it systematically arranges, groups, and labels cataloged data, making it easy for users to locate the exact information they require.   AI-generated image: ChatGPT 4o.   What Makes Up the Glossary?   The glossary is built on terms and term types.   Terms A term is the fundamental building block in the glossary. It helps you capture the essential information.     What attributes you can capture is defined by a term type.   Term Types Term types function as templates, aiding in the organization of terms into meaningful functional groups based on your business needs. This structure allows for customization and capturing specific details.     Linking Terms to Data Assets Terms can be attached to columns of cataloged data assets.       How to Use the Glossary? The glossary is flexible and adaptable, with terms structured in hierarchies, providing an immediate overview of the information.     The hierarchy can be organized based on business units, processes, information catalogs, or data views. This versatile structure aligns with your organization's specific data governance objectives.   Terms can be organized using various structures, such as:   Organizational View: This follows the structure of Business Unit > Department > Business Term. Process View: This is arranged as Business Process > Process Step > Business Term. Information Catalog View: This is set up as Subject Area > Domain > Business Term. Data View: This adopts the structure of Area > Entity > Attribute.   Who Can Use the Glossary?   The glossary is accessible to catalog users and glossary administrators.   The catalog users can consult the terms, attach data asset columns to a term, while glossary administrator can manage term types, create, update and delete terms and even import terms from a CSV file.   The Glossary REST API allows you to manage all aspects of the glossary from the comfort of a command line or a program written in SAS, Python. The Glossary REST API is a great tool that can help you automate all things related to a glossary.   The Glossary REST API provides the convenience of managing all aspects of the glossary directly from a command line, a shell script, or a program written in SAS or Python. This powerful tool facilitates automation of all glossary-related tasks.   The Bigger Picture   The glossary forms an integral part of the catalog. It works in conjunction with other features like asset tags, descriptions, and contacts to provide a comprehensive and clearer picture of the data. It is not a standalone tool but a key part of the data governance ecosystem.     Conclusion   In conclusion, the glossary in SAS Information Catalog is a vital tool for data governance and data management. It adds a semantic layer of knowledge in natural language, making data interpretation faster and more accurate. Its integration with other catalog functionalities like asset tags, descriptions, and contacts further enhances data understanding.   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.   More SAS Information Catalog Posts Boost Data Governance with SAS Information Catalog OpenMetadata Connector. 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. Discover Your Data with SAS Information Catalog APIs from Python – Upload to CAS. Using Azure OpenAI's GPT-4 to Query Cataloged Assets (combining LLMs and Catalog Instances APIs).   Find more articles from SAS Global Enablement and Learning here. ... View more

As of the 2024.01 version, the SAS OpenMetadata connector can be utilized for the automatic addition of SAS Information Catalog metadata into an OpenMetadata repository. In this article, we'll present a brief video guide on how the SAS Information Catalog assets appear in OpenMetadata. We’ll take a look at the steps to achieve this integration.   What Assets?   The OpenMetadata repository can include metadata from the following catalog assets:   SAS libraries SAS tables. SAS columns. CAS libraries. CAS tables. SAS Studio flows. SAS Visual Analytics reports.   However, this connector does not support writing metadata back to the SAS Information Catalog repository.       Why OpenMetadata Integration?   SAS customers have long sought a method to integrate SAS assets with those from other applications. OpenMetadata provides various connectors for this purpose. Now, it's possible to export assets from SAS to OpenMetadata. Here, assets from different applications can be linked using tags, terms, domains, or manual lineage, among other options.       Steps   Procedure to Incorporate SAS Information Catalog Assets into OpenMetadata:   Initiate OpenMetadata Deployment. Options: Docker deployment (I had more success with it). Kubernetes deployment (I had some issues with port forwarding). Set up the SAS Open Metadata Connector. Import Metadata from SAS Information Catalog. ExploreMetadata.   This article will primarily concentrate on metadata exploration. Subsequent posts will delve into details about deployment, connection, and the ingestion process.       Watch the Short Video     Conclusions   The OpenMetadata connector for the SAS Information Catalog facilitates enhanced asset integration, including assets from various catalogs. In the face of numerous compliance and data protection regulations, data governance features like lineage, tagging, attaching terms, policies, and owners to assets are beneficial.   They help solve a few problems. These features in OpenMetadata allow for the creation of a comprehensive map detailing the assets and their interactions in data management processes.   Discussion   Personally, I'm delighted to see that SAS has opted for an open-source product for external asset integration. This product is easy to set up and features numerous connectors. Furthermore, Airflow efficiently manages the metadata ingestion process.   Please comment and let us know what you think about OpenMetadata, the integration, what problems it solves, what problems it doesn't solve, what the future direction should be. It's always nice to hear your opinion and personal experience.   What to Read Next   SAS Information Catalog: Administrator’s Guide OpenMetadata SAS Connector. More SAS Information Catalog Posts Boost Data Governance with SAS Information Catalog OpenMetadata Connector. 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. Discover Your Data with SAS Information Catalog APIs from Python – Upload to CAS. Using Azure OpenAI's GPT-4 to Query Cataloged Assets (combining LLMs and Catalog Instances APIs).   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

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