Trending December 2023 # How To Use Sas Libname With Statement? # Suggested January 2024 # Top 17 Popular

You are reading the article How To Use Sas Libname With Statement? updated in December 2023 on the website We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Use Sas Libname With Statement?

Introduction to SAS Libname

The SAS libname is associated with the named as the associates or disassociates the SAS library with by using the shortcut keys like libref that include the characteristics of the SAS library concatenate with the SAS libraries including the SAS catalogs that moved to the SAS Global Statements with the temporary name associated with the physical name of the SAS data library to read or write the data sets.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is SAS Libname? How to Use SAS Libname?

SAS library is to define the Libname statement for assigning the libref with the global statement on the Interactive SAS session, which remains assigned until they cancel or change the libref. It is a collection of one or more number of SAS files recognized by the SAS libraries that are more referenced and storage units.

At the beginning SAS session, the SAS will automatically create the two libraries that can access the work as the temporary library and other SAS users are the permanent library. The SAS dataset is the table view of the rows and columns according to the variables path list is locked down state, which aggregates the data storage location on the SAS file Storage. Suppose any SAS name is assigned to a new libref and disassociated to the libref from the SAS library to list the attributes previously assigned to the library specification with a combination of the two datas to the SAS session. In addition, it has a built-in temporary library called the Work library to store the place placed in the current session for losing datasets created and stored in the work library for closing the SAS session.

The data step will start with the data work using the Stats Rule on the work library. It is beneficial to manipulate the dataset in multiple steps with necessary actions. Data sets with interim steps for the work library to help the stored data sets with nervous manipulating the original file from the dataset into the work library and can manipulate it.

Steps to use SAS libname

1. After login the SAS OnDemand for Academics Dashboard website.

3. Choose SAS Studio on the Applications UI.

4. Navigate to the SAS Studio Application and choose the Libraries.

7. Libraries have all the required datas and classes.

Using Libraries in SAS

Depending on the library name, the SAS file will be created, such as the SAS data set, and it will be stored in either temporary or permanent data libraries. It will create a SAS file and will use the library name to work with the specified library name in all the files that will be temporary SAS libraries. Libref is the data label and alias for temporarily assigned folder location on the full path name, including drive and folders to the form, which is recognized by the SAS system. It exists only during the user session, which is created already on the logical concept that describes the physical location stored with the file. Follow the rules for SAS users on the language reference with a dictionary for more information on SAS data libraries.

Access the new library dialog box using the GUI librefs with the new library toolbar icon from the LIBASSIGN command using the explorer window whenever it will use all of the GUI options from the New Library dialog box to specify the librefs and multiple folders engines if the second explorer window is on the right side of the libassign command box of the SAS workspace. The hard-coded file path has two pieces of data information, namely the file location along with the name and type of the data. SAS library statement starts with the LIBNAME keyword followed by the library name. Libref is one of the engines. Finally, the location of the files running with the global and RUN statements and deleting the SAS library automatically if the SAS session starts the statement. It will delete the libref using the required syntax with the Clear keyword.


Libname first clear; SAS Libname Statement

The SAS associates or disassociates from the SAS library with a libref for the shortcut statement, which clears all the librefs and characteristics lists of the SAS library. It allows SAS libraries to concatenate the SAS catalogs that moved to the SAS Global statements. The user will enable the interactive SAS session with libref and assign the statement which remains assigned to change the SAS session. It will mainly follow the same rules, like four types of rules, not more than the eight characters required; the first set of characters should be the letter, followed by the subsequent set of characters along with letters, numbers, and underscore symbols.



libname June2 sqlsvr noprompt="uid=siva; pwd=raman; dsn=sqlservr;" stringdates=yes; proc print data=June2.testemployees; where city='MAS'; run;



SAS libref is one of the main libraries from the user SAS session, and it will be used automatically from the SAS session or with the Libname keyword statement. It refers to the specified set of keywords in data formats like excel, XML, and SPSS. If we delete the libref only, we cannot delete the datas from the SAS code.

Recommended Articles

This is a guide to SAS Libname. Here we discuss the introduction and how to use SAS libname with the statement. You may also have a look at the following articles to learn more –

You're reading How To Use Sas Libname With Statement?

How To Use Variables Sas Rename With Example?

Introduction to SAS Rename

The SAS rename is the feature for changing the variable’s name or the variable list already declared in the SAS input data set or in the data step created by the new set of variables. It allows you to replace old variable names in programming statements by changing one or more variables written into the output directory.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Overview of SAS Rename

The rename is a type of function that can be used for more than one variable in a SAS dataset, and SAS users will refer to it as such. Renaming column variables will use the datas like NHANES, but the code of the data registration is not correctly called for the column names. This is nearly impossible for the recoding data columns. It decodes the datas by using the more straightforward format that assists and is associated with the sequence number of the SAS issues and the spaces between the original data content and duplicate contents.

We can enclose the text from the data label using the single and double quotation Rename option, which tells SAS to change the name of the two variables from within the parentheses as long as the space separates each pair of the old and new variables.

How to Use Variables SAS Rename?

We can use the rename function with the SAS dataset for the column variables from the NHANES data, but the data code is registered incorrectly. The specified column names are so long, and it’s nearly impossible to recode the data from one format to another more straightforward format. Respondent and correct sequence numbers to the ID with SAS having issues on the spaces from the original name to the duplicate name with exact sequence steps.

Steps to create the variable SAS rename:

1. Navigate to the below link.

3. After successfully logging in, type the below code for creating the data set and Proc SQL.

4. data First;

5. input emp_id $ emp_name $ emp_dob $;

6. datalines;

7. 1234 Raman 10051989

8. 5 Srew 1234521

9. 6 rtw 10454

10. ;


12. proc sql;

13. insert into First

14. set emp_id=1234,

15. emp_namename=’Raman’,

16. emp_dob=10051989

18. the title “Welcome To My Domain”;

19. select emp_name format=$20.,

20. emp_id format=$15.,

21. emp_dob format=comma15.0

22. from First;


It helps create the dataset along with PROC SQL with the variable declaration.

Then by using the below code to change the variable or column name like emp_name, it is changed to name by using the below code.


data First; set First; rename emp_name = Name; run;


The name is successfully changed and displayed on the output tab.

SAS Rename Statement

The SAS rename statement is specified with the new names for the required variables in the output console for SAS data sets; it is valid in the DATA step. The statement category is the CAS, denoted by the data and information, and it’s declared using the Declarative type. Rename parameters like old and new names with the specified arguments and enable the name change for one or more variables on the list that will combine the variables and variable lists. The new variable name is declared by the user end. Then, it will be executed and written into the output directory dataset by using the old variable names in programming statements for the current data step. Rename the values and apply them to all the output data sets.

Generally, the Rename statement, which affects the datasets, is already opened in the output mode, which must continue to rename the old variable names in the programming statements for the current DATA step. After the output data is created, the SET statement’s new variable names rename the input data set variables. We can set and use the new names in the programming statements of the current data step of renaming the variables, which helps to write the datas in the file management task. Using the datasets procedure or accessing the variables through the SAS windowing interface for the method is more straightforward and does not require data step processing. Using the change statement, we can call the datasets procedure for renaming the variables for setting the same library in the SAS.

Example of SAS Rename

Given below is the example of SAS Rename:


DATA Second; INPUT Rollno $ ID $ Name $; DATALINES; 001 9184321123 Siva 002 9212434 Raman 003 9351251 Sivaraman ; RUN; PROC SQL; CREATE TABLE bckup AS SELECT * FROM Second; QUIT; PROC PRINT vars = bckup; RUN;



data Second; set Second; rename ID = MobileNumber; run;


The above codes are the primary example for renaming the variables, columns, and data types.

Here the Rename option uses the dataset option to pass the input dataset along with the variable SET statement and the output dataset.

Create a table on the proc sql using the rename keyword; we can rename the old value to a new value.


The SAS Rename is the type of option that can be used in the dataset option for both the input dataset, which passed the SET statement, and the output dataset. The Rename option will follow the keep and drop options using the existing variable name.

Recommended Articles

This is a guide to SAS Rename. Here we discuss the introduction, how to use variables SAS rename? And examples, respectively. You may also have a look at the following articles to learn more –

Performing Exploratory Data Analysis With Sas And Python

This article was published as a part of the Data Science Blogathon

Hi all, this is my first blog hope you all like it and can learn, gain some information out of it!


In this blog, we will try to understand the process of EDA(Exploratory Data Analysis) and we will also perform a practical demo of how to do EDA with SAS and Python.

The dataset that I will be using is the bank loan dataset which has 100514 records and 19 columns. I took this big dataset so that we could learn more from it rather than just using a small dataset for our project which would not be so much fun and learning.

This blog that I’m writing is just for making you’ll understand the way things work in both of these tools (i.e SAS and Python), which are undoubtedly the top best tools out in the market for Data Science and Analysis. You can always tweak the code and make it much more dynamic and reliable.

I have seen that there are ample resources available out there for Python, but this is not the same case with SAS, So I thought of giving you a clearer picture of how things work in SAS.

Let’s get started and dive deep into this process…

Exploratory Data Analysis

First, we’ll answer few questions about this process-

What is EDA?-

Why do we use it?-

How to perform this process?

Answer(1)- It is a process in which we get to know our data by summarizing the information and even using graphical charts to better visualize our data.

Answer(2)- It’s a building block of a data science life cycle and will further help us determine the properties, importance of variables, and build a more robust model for our dataset.

Answer(3)- We just need to select our dataset and perform some basics operations on it like Identification of variables and their data types, Descriptive statistics of our dataset, Finding Missing values, Checking for Unique values in our data, Univariate and Bivariate Analysis, etc… 

So now we will start with practical examples for SAS followed by Python and I’ll mention codes for both of the tools simultaneously for each process…..

In the graphical visualizations part, I haven’t used all variables for demonstrating different types of visualizations I just have used a few no. of variables because it increases the processing time as our data is large, and the second and most important thing is that I don’t want to make this blog too long for viewers to read. My main motive is to make them understand how things work so they can relate to it and further scale their knowledge and write code efficiently.

Identification of variables and data types-

Here we will look at the metadata of our dataset with the type of variables present in it.

SAS Code-

/* Checking the contents of the dataset */ proc contents data=blog.credit_train; run; /* Taking a look at the dataset for only 50 records */ proc print data=blog.credit_train obs=50; run;


Contents of the dataset-

Viewing only 100 records from the dataset as the original no is too huge (i.e 1 lakh+)-

Here only 11 records are captured in the image while we can scroll it further when implementing.

Python Code-

# Importing the libraries for the blog's dataset import numpy as np import pandas as pd import matplotlib.pyplot as plt %matplotlib inline import seaborn as sns # Contents of the data and shape of the data # Reading the dataset df = pd.read_csv('C:UsersAspire 5Desktopcreditcredit_train.csv') df df.head(50) Output-

Contents of the data and shape of the dataset-

Reading the dataset-

Descriptive Statistics of our dataset-

Here we will check the mean, median, mode, etc… for our dataset

SAS Code-

/* Descriptive statistics of our dataset */ proc means data=blog.credit_train mean median mode std var min max;



Descriptive stats-

Python Code-

# Descriptive statistics of our dataset df.describe()


Descriptive stats-

Finding missing values-

We will check the no. of missing values in each column.

SAS Code-

/* Missing values in our dataset */ proc means data=blog.credit_train nmiss; run;


Missing Count-

Python Code-

# Total no of missing values in each column df.isnull().sum()


Missing values count in each column-

Unique values in our data-

We are checking for unique values in our dataset to get familiar with how much value a variable truly holds out of all the records, which may help in normalizing the data if the values are too less.

Unique values for columns-

SAS Code-

Here is the unique value for 5 columns but we can scale it for more columns just by adding the count distinct statement…

/* Unique values for 5 columns */ proc sql; select count(distinct 'Loan Status'n) as 'Loan Status'n, count(distinct Bankruptcies) as Bankruptcies, count(distinct Term) as Term, count(distinct 'Credit Score'n) as 'Credit Score'n, count(distinct 'Monthly Debt'n) as 'Monthly Debt'n from blog.credit_train; quit;


Python Code- # Total count of unique values for each column a = pd.DataFrame(df) a.nunique()


Univariate Analysis-

It is a part of statistical analysis where we take into consideration a single variable and perform analysis on it


It is the representation of distribution for numerical data.

SAS Code-

Here I have used only a single variable just to show you, but you all can do it for multiple variables furthermore you can use another procedure for doing so I’ll list both of the procedures below.

/* You can give multiple variables in this procedure to create histograms */ proc univariate data=blog.credit_train novarcontents; histogram 'Current Loan Amount'n 'Credit Score'n / ; run; /* Creating histogram with only one variable (i.e Credit Score) */ ods graphics / reset width=6.4in height=4.8in imagemap; proc sgplot data=BLOG.CREDIT_TRAIN; histogram 'Credit Score'n /; yaxis grid; run;


Here the output is from the second procedure (i.e proc sgplot) for the Credit Score variable…

Python Code-

# Creating histogram for Credit Score variable plt.hist(df['Credit Score']) plt.xlabel('Credit Score') plt.ylabel('Count')


Bivariate Analysis-

It is a part of statistical analysis where we take into consideration multiple variables and perform analysis on them.

Scatter plot-

It is used to check the relationship between numeric variables.

SAS Code-

/* Checking Relationship between two variables by using scatter plot */ ods graphics / reset width=6.4in height=4.8in imagemap; proc sgplot data=BLOG.CREDIT_TRAIN; scatter x='Number of Open Accounts'n y='Current Credit Balance'n /; xaxis grid; yaxis grid; run; ods graphics / reset;


Python Code-

# Creating a scatter plot to observe relationship between numeric variables sc = sns.scatterplot(data=df, x="Number of Open Accounts" ,y="Current Credit Balance") sc



It says how the variables are correlated with one another, which will further help take important variables into account for model building.

SAS Code-

/* Correaltion among numeric variables */ ods noproctitle; ods graphics / imagemap=on; proc corr data=BLOG.CREDIT_TRAIN pearson nosimple noprob plots=none; var 'Current Loan Amount'n 'Credit Score'n 'Annual Income'n 'Monthly Debt'n 'Years of Credit History'n 'Number of Open Accounts'n 'Number of Credit Problems'n 'Current Credit Balance'n 'Maximum Open Credit'n Bankruptcies 'Tax Liens'n; run;


Python Code-

# Correlation among variables df.corr() #Displaying numerical values df_corr = df.corr() #Generating Graphical visualization sns.heatmap(df_corr, xticklabels = df_corr.columns.values, yticklabels = df_corr.columns.values, annot = True);


Displaying numerical values as output-

Generating graphical visualization as output-


It is used to assess the outliers in our dataset for variables as having outlier’s results in degrading our model performance and lack of accuracy.

SAS Code-

/* Box plot for checking outliers in the data */ ods graphics / reset width=6.4in height=4.8in imagemap; proc sgplot data=BLOG.CREDIT_TRAIN; vbox 'Credit Score'n / category='Loan Status'n; yaxis grid; run; ods graphics / reset;


Python Code-

# Creating a boxplot for a variable ax = sns.boxplot(x='Loan Status', y='Credit Score', data=df ) ax


Let me give you all a short summary about myself so I’m a graduate in Statistics and now aspiring to become a SAS Data Scientist while I have already gained some skills and Certifications around this domain such as Big Data Professional, Machine Learning Specialist, etc…

End Notes

I am now looking forward to writing blogs and further expand my knowledge and help other people to understand the things hovering around this domain. I’m also an active sports player(football) who likes to play and stay fit.

Here’s my LinkedIn id if you all want to connect… till then happy learning!

The media shown in this article are not owned by Analytics Vidhya and are used at the Author’s discretion.


How To Use Powershell Empire With Installations?

Introduction to PowerShell Empire

PowerShell Empire can be implied as a post-exploitation agent. PowerShell Empire implementation can execute on agents of the PowerShell without any support of chúng tôi keyloggers, mimikatz, or other modules. It has the adaptive communication to escape network detection. It can be clubbed into a framework that is accessible from GitHub. As the source PowerShell Empire is unavailable, it can be forked from GitHub by BC security.

Start Your Free Data Science Course

Key Takeaways

The main operation of PowerShell Empire is to:

Create a listener to connect with the confronted host.

Develop a stager for that listener to upload the agent.

Prepare a payload for the remote host or create an agent.

The agent defines the module to accomplish certain goals.

What is PowerShell Empire?

PowerShell Empire is a post-exploitation agent and can execute on all the PowerShell agents. It can be installed without support from modules, keyloggers, and chúng tôi It has an adaptive communication to escape network detection, and it bundles every framework and places it on GitHub, which can be accessed as a public source. The invader exploits any windows server through some unknown pattern and utilizes all the frameworks in PowerShell to work on the objectives. Then the server returns the response or information which the attacker wants.

How to Use PowerShell Empire?

Some of the actions can be achieved with privilege escalations that are escalating the privileges from a standard account to an admin account or finding out where the host and services are present, which is called the host and network reconnaissance, lateral movement between the host, and credential gathering. All these are essential elements for trending penetration tests. The PowerShell Empire has three main components: agents, stagers, and listeners.

The listener is a method that snoops for the connection from the host machine yet to be attacked and helps the Empire send back the loot to the invader’s computer. A stager is a code snippet that enables the malware code to execute on the agent, which is the compromised host. Here agent is a software program that manages the connection between the computer and the host that is compromised. So modules are used to execute the malware commands, which find the credentials, elevate the privileges, and crack the machine.

Installations of PowerShell Empire

Executing the PowerShell requires a kali Linux OS machine, where the Kali is best for hacking. To install PowerShell Empire on a Linux machine, clone the source from GitHub.

Open the terminal and give the below command:


To create a new directory, give the name Empire and move the guide by providing cd Empire. So that directory is changed and then passes the ls command to list the contents in the directory.

Now the user can read the data by cat command in the readme. Md file.

Type ./ chúng tôi to install the Empire tool by executing the script. The user is asked to configure the server negotiating password during installation. To provide a strong password. So these are the installation steps, and now you can execute the PowerShell Empire.

Navigate to the Empire directory by providing the cd.. command and execute the ./ Empire executable script. If the Empire leaves an error at the time of initializing, then move to the setup folder. And now, with cd setup, execute the ./ chúng tôi script. Now restart the Empire again like in the previous steps, and now if required, the user can install missing modules like listeners, stagers, agents, and other dependencies.

PowerShell Empire Command

Listeners present in Empire are the structured channels to receive connections from the target host machine. So before working with Empire, start the listeners first. In listener management, give a help command that shows a few essential commands.

Agents – Enable the user to navigate to the agent menu.

Back and main – Enables to navigate to the main menu.

Exit – Exit from the Empire.

Info – Displays the information about the working listeners.

Kill – Terminate a particular listener.

Launcher – Produce an initial launching console for the listener.

List – Detailed view of active listeners.

Launcher – Used to generate an initial launcher for a listener.

PowerShell Empire Tool

PowerShell Empire allows the attacker to execute the commands in memory; it insists that the malware attack happens only on the PowerShell Empire and cannot be performed on the hard drive. So it reduces the risk of being caught in antivirus software and leaves digital prints to help forensic investigators.


Other FAQs are mentioned below:

Q1. Do we use PowerShell Empire?

Answer: The source code, which is the Empire project, is not maintained. But it can be forked from the GitHub repository.

Q2. What is replaced now with PowerShell Empire?

Answer: It can be replaced with Python 3.

Q3. Who is the inventor of the Empire tool?

Answer: Milwaukee is the developer and manufacturer of Empire tools. It is the brand of Techtronic industries.


Hence, PowerShell Empire was a renowned post-exploitation agent in the malware attack and was configured as per the requirement. It enables the user to execute the PowerShell script and develop a connection back to the host machine.

Recommended Articles

This is a guide to PowerShell Empire. Here we discuss the introduction, Installations, and how to use PowerShell Empire with commands and tools. You may also have a look at the following articles to learn more –

How To Use Keras Model With Examples?

Introduction to Keras Model

Keras models are special neural network-oriented models that organize different layers and filter out essential information. The Keras model has two variants: Keras Sequential Model and Keras Functional API, which makes both the variants customizable and flexible according to scenario and changes. Moreover, it makes the functional APIs give a set of inputs and outputs with a single file, giving the graph model’s look and feel accordingly. It is a library with high-level language considered for deep learning on top of TensorFlow and Theano. It is written in Python language.

Start Your Free Data Science Course

What is Keras Model?

Keras model is used for designing and working with neural network types that are used for building many other similar formats of architecture possessing training and feeding complex models with structures. It comprises many graphs that support the representation of a model in some other ways, with many other configurable systems and patterns for feeding values as part of training. Moreover, it provides modularity, which helps make flexible and well-suited models for customization and support. Two approaches based on this help develop sequential and functional models.

How to Use Keras model?

As the Keras model is a python-based library, it must be used for flexibility and customized model design, especially for prediction.

Keras model has its way of detecting trends with behavior for modeling and prediction.

Keras model uses a model.predict() class and reconstructed_model.predict(), which have their own significance.

Predict () class within a model can be used for creating and fitting trained data using prediction.

Another class, i.e., reconstructed_model.predict() within a model, is used to save and load the model for reconstruction. A reconstructed model compiles and retains the state into optimization using either historical or new data.

Certain components will also get incorporated or are already part of the Keras model for customization, which is as follows:

Optimizer: It is used for compiling a Keras model by passing certain arguments containing the Optimizer loss function to minimize and handle any unpredictable loss.

Loss of set and metrics: A model is compiled and is used in a way where it is used for including losses and metrics that will get taught at the time of training or modeling.

Weights: Certain input parameters must be fed to the model for required output within a Keras model.

Create Keras Model

Ways to create a model using Sequential API and Functional API

1. Using Sequential API

The idea is to create a sequential flow within layers that possess some order and help make certain flows from top to bottom, giving individual output. It helps in creating an ANN model just by calling a Sequential API() using the Keras model package, which is represented below:

model_any = sequential()

The next step is to add a layer for which a layer needs to be created, followed by passing that layer using add() function within it

model_any.add( inpt_layer)

Next, Is to access the model: which means to provide the relevant information about the layers defined or added as part of the model.


Model. input: will provide all relevant input then similarly model. the output will give relevant information about the same.

Serializing the model is another important step for serializing the model into an object like JSON and then loading it like

Then, the Summarization of the model happens, followed by Training and prediction of the model, which include components like compile, evaluate, fit, and predict.

2. Using Functional API

Functional API is an alternative to Sequential API, where the approach is almost identical. Still, it does support and gives flexibility in terms of a certain complex model where an instance is created first, followed by connecting the layers with an input or output.

Let’s create a model by importing an input layer.

Creating an input layer where we can define dimensional input shape for a model is as follows:


Add a dense layer for the input


Define your model accordingly:

Create a model with both input and output layers using functional API:

model=Model(inpt=data, otput=layer)

Keras Model Types

Keras model represents and gels well with Deep learning; it gives the following ways to generate model types:

1. Sequential Type Model

As its name suggests, the sequential type model mostly supports and creates sequential type API, which tries to arrange the layers in a specific sequence and order.

Most deep learning and neural network have layers provisioned in a sequence for transferring data and flow from one layer to another sequence data.

2. Functional API model

This model is used to create and support some complex and flexible models.

This also helps make Directed acyclic graphs (DAGs) where the architecture comprises many layers that need to be filtered from top to bottom.

It also helps define and design branches within the architecture with some inception blocks, functions, etc.

Highlight a few famous examples supporting the Functional API model Squeeze Net, Xception, ResNet, GoogleNet, and Inception.

3. Model Subclassing

Model subclassing is a way to create a custom model comprising most of the functions and classes that are the root and internal models to the full custom forward pass model.

It does help in assisting and supporting Functional or sequential types of models for manipulation and testing.

Examples of Keras Model

Below are the different examples of the Keras Model:

Example #1

x_val_0 = x_train_0[-10020:] y_val_0 = y_train_0[-10010:] x_train_0 = x_train_0[:-10000] y_train_0 = y_train_0[:-10060] print(“prediction shape:”, prediction.shape)

Example #2

This program represents the creation of a model using Sequential API ().


Example #3

This program represents the creation of a model with multiple layers using functional API()



Keras model is used for a lot of model analysis related to deep learning and gels well with all types of the neural network, which requires an hour as most of the task carried out contains an association with AI and ANN. Tensorflow, when incorporated with Keras, makes wonder and performs quite well in analysis phases of different types of models.

Recommended Articles

This is a guide to Keras Model. Here we discuss the definition, how to use and create Keras Model, and examples and code implementation. You may also have a look at the following articles to learn more –

How To Use And Work With Trim Function?

Introduction to PL/SQL TRIM

PL/ SQL trim is the function provided in PL/ SQL Database Management System which helps to remove all the trailing or leading blank spaces or any other character if required. This function is mostly used to make sure that the data being stored in the column field is as much optimized as possible and does not contain any other blank spaces at starting or ending of the string being stored. However, this function can also be used in other scenarios where we have a requirement to remove the initial or ending occurrence of a particular character in the string. In this article, we will study the general syntax, usage, and implementation of PL/ SQL trim function along with the help of certain examples.

Start Your Free Software Development Course

Syntax and Working

The syntax of the PL/ SQL trim function is as shown below:

In the above syntax, the terms that are used and the working of trim function when they are specified or not specified is as explained below:

TRAILING – Whatever source string is supplied in the input, if we want to remove all the occurrences of the character to be removed parameter from the source string that occurs in the ending portion of the source string by specifying the keyword TRAILING after TRIM () function inside it as the first parameter. If the characters at the end match with the character to be trimmed then it is removed from the end of the source input string.

LEADING– Whatever source string is supplied in the input, if we want to remove all the occurrences of the character to be removed parameter from the source string that occur in the beginning portion of the source string by specifying the keyword LEADING after TRIM () function inside it as the first parameter. If the characters at the beginning match with the character to be trimmed then it is removed from the start of the source string.

BOTH – When we specify the keyword BOTH in the first parameter of the TRIM syntax then all the matching character occurrences that have equal value as that of the character to be trim parameter are removed from the beginning and the ending of the source string. By default, if we don’t specify any value in the first parameter then it is considered as BOTH.

Character to be trimmed – This character is considered for doing a match by oracle database to select which characters need to be removed. In case, if this parameter is not specified then the default value considered is blank space and the same value is considered as the character to be trimmed.


The string format is returned as the output of the input string. Whatever supplied string is processed for removal of the occurrences of the character to be trimmed is treated as the output.

Points to be considered:

In case if only one parameter is supplied in the trim () function then the DBMS removed all the trailing and leading occurrences of the blank spaces in the supplied parameter of the trim () function as this parameter is considered as the source string. If we try to specify either the source string ass null or the character to be trimmed as null the output of the trim function is null itself.

Support for Oracle/ PLSQL versions for TRIM() function:

The following versions of ORACLE support the usage of the TRIM() function:

Oracle 8i

Oracle 9i

Oracle 10g

Oracle 11g

Oracle 12c

Advantages of PL/SQL TRIM Examples of PL/SQL TRIM

Let us consider some examples which will help us to understand the implementation of the TRIM () function in PL/ SQL.

Example #1

Let us first consider an example that will help us to demonstrate how we can remove the LEADING occurrences of the particular character in the source string. Consider one string “33EDUCBA33” from which we want to remove the 3 characters from the beginning of the string. In such a case, we can make the use of following query program in PL/ SQL –

DECLARE sample input string(20) := '33EDUCBA33'; BEGIN dbms_output.put_line(TRIM(LEADING '3' FROM sampleInput)); END;

The output of the execution of the above program is as shown below:

Example #2

Now, let us consider the same input string and we will use the TRIALING parameter as the first parameter of the TRIM () function. This will lead to the removal of all the occurrences of the ending characters from the source string. The program for doing so in PL/ SQL will be as shown below:

DECLARE sampleInput string(20) := '33EDUCBA33'; BEGIN dbms_output.put_line(TRIM(TRAILING '3' FROM sampleInput)); END;

The output of the execution of the above program is as shown below:

Example #3

Let us consider an example where we will be using BOTH as the first parameter to the trim () function. In this case the program will become as shown:

DECLARE sampleInput string(20) := '33EDUCBA33'; BEGIN dbms_output.put_line(TRIM(BOTH '3' FROM sampleInput)); END;

The output of the execution of the above program is as shown below:

In the above program even if we don’t specify any parameter in first place like TRAILING, LEADING, or BOTH still the function will remove the beginning and ending occurrences of the character to be trimmed from the source string.


The trim () function is used in PL/ SQL to remove any of the occurrences of the particular character in either the beginning or the ending of the source string or even in both. This function is mostly used for efficient use of space in database while storing the strings which removes all the occurrences of blank spaces if present at the end of the string. Any character at end of the beginning can be removed when used properly.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL TRIM” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Update the detailed information about How To Use Sas Libname With Statement? on the website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!