Leonid Batkhan

4月 142021

Improving programming jobs performance with massively parallel processingUntil recently, I used UNIX/Linux shell scripts in a very limited capacity, mostly as vehicle of submitting SAS batch jobs. All heavy lifting (conditional processing logic, looping, macro processing, etc.) was done in SAS and by SAS.  If there was a need for parallel processing and synchronization, it was also implemented in SAS. I even wrote a blog post  Running SAS programs in parallel using SAS/CONNECT®, which I proudly shared with my customers.

The post caught their attention and I was asked if I could implement the same approach to speed up processes that were taking too long to run.

However, it turned out that SAS/CONNECT was not licensed at their site and procuring the license wasn’t going to happen any time soon. Bummer!

Or boon? You should never be discouraged by obstacles. In fact, encountering an obstacle might be a stroke of luck. Just add a mixture of curiosity, creativity, and tenacity – and you get a recipe for new opportunity and success. That’s exactly what happened when I turned to exploring shell scripting as an alternative way of implementing parallel processing.

Running several batch jobs in parallel

UNIX/Linux OS allows running several scripts in parallel. Let’s say we have three SAS batch jobs controlled by their own scripts script1.sh, script2.sh, and script3.sh. We can run them concurrently (in parallel) by submitting these shell scripts one after another in background mode using & at the end. Just put them in a wrapper “parent” script allthree.sh and run it in background mode as:

$ nohup allthree.sh &

Here what is inside the allthree.sh: 

script1.sh &
script2.sh &
script3.sh &

With such an arrangement, allthree.sh “parent” script starts all three background tasks (and corresponding SAS programs) that will run by the server concurrently (as far as resources would allow.) Depending on the server capacity (mainly, the number of CPU’s) these jobs will run in parallel, or quasi parallel competing for the server shared resources with the Operating System taking charge for orchestrating their co-existence and load balancing.

The wait command at the end is responsible for the “parent” script’s synchronization. Since no process id or job id is specified with wait command, it will wait for all current “child” processes to complete. Once all three tasks completed, the parent script allthree.sh will continue past the wait command.

Get the UNIX/Linux server information

To evaluate server capabilities as it relates to the parallel processing, we would like to know the number of CPU’s.

To get this information we can ran the the lscpu command as it provides an overview of the CPU architectural characteristics such as number of CPU’s, number of CPU cores, vendor ID, model, model name, speed of each core, and lots more. Here is what I got:

Ha! 56 CPUs! This is not bad, not bad at all! I don’t even have to usurp the whole server after all. I can just grab about 50% of its capacity and be a nice guy leaving another 50% to all other users.

Problem: monthly data ingestion use case

Here is a simplified description of the problem I was facing.

Each month, shortly after the end of the previous month we needed to ingest a number of CSV files pertinent to transactions during the previous month and produce daily SAS data tables for each day of the previous month.  The existing process sequentially looped through all the CSV files, which (given the data volume) took about an hour to run.

This task was a perfect candidate for parallel processing since data ingestions of individual days were fully independent of each other.

Solution: massively parallel process

The solution is comprised of the two parts:

  • Single thread SAS program responsible for a single day data ingestion.
  • Shell script running multiple instances of this SAS program concurrently.

Single thread SAS process

The first thing I did was re-writing the SAS program from looping through all of the days to ingesting just a single day of a month-year. Here is a bare-bones version of the SAS program:

/* capture parameter &sysparm passed from OS command */ 
%let YYYYMMDD = &sysparm;
/* create varlist macro variable to list all input variable names */
proc sql noprint;
   select name into :varlist separated by ' ' from SASHELP.VCOLUMN
   where libname='PARMSDL' and memname='DATA_TEMPLATE';
/* create fileref inf for the source file */
filename inf "/cvspath/rawdata&YYYYMMDD..cvs";
/* create daily output data set */
   if 0 then set PARMSDL.DATA_TEMPLATE;
   infile inf missover dsd encoding='UTF-8' firstobs=2 obs=max;
   input &varlist;

This SAS program (let’s call it oneday.sas) can be run in batch using the following OS command:

sas oneday.sas -log oneday.log -sysparm 202103

Note, that we pass a parameter (e.g. 202103 means year 2021, month 03) defining the requested year and month YYYYMM as -sysparm value.

That value becomes available in the SAS program as a macro variable reference &sysparm.

We also use a pre-created data template PARMSDL.DATA_TEMPLATE - a zero-observations data set that contains descriptions of all the variables and their attributes (see Simplify data preparation using SAS data templates).

Shell script running the whole process in parallel

Below shell script month_parallel_driver.sh puts everything together. It spawns and runs concurrently as many daily processes as there are days in a specified month-of-year and synchronizes all single day processes (threads) at the end by waiting them all to complete. It logs all its treads and calculates (and prints) the total processing duration. As you can see, shell script as a programming language is a quite versatile and powerful. Here it is:

# cd /projpath/scripts
# nohup sh month_parallel_driver.sh &
# Project path
# Program file name
# Current date/time stamp
now=$(date +%Y.%m.%d_%H.%M.%S)
echo 'Start time:'$now
# Reset timer
# Get YYYYMM as the script parameter
# Extract year and month from $par
# Get number of days in month $m of year $y
days=$(cal $m $y | awk 'NF {DAYS = $NF}; END {print DAYS}')
# Create log directory
mkdir $logdir
# Loop through all days of month $m of year $y
for i in $(seq -f "%02g" 1 $days)
   # Assign log name for a single day thread
   # Run single day thread
   /SASHome/SASFoundation/9.4/sas $pgmname -log $logname -sysparm $par$i &
# Wait until all threads are finished
# Calculate and print duration
end=$(date +%Y.%m.%d_%H.%M.%S)
echo 'End time:'$end
mm=$(( $(($SECONDS - $hh * 3600)) / 60 ))
ss=$(($SECONDS - $hh * 3600 - $mm * 60))
printf " Total Duration: %02d:%02d:%02d\n" $hh $mm $ss
echo '------- End of job -------'

This script is self-described by detail comments and can be run as:

cd /projpath/scripts
nohup sh month_parallel_driver.sh &


The results were as expected as they were stunning. The overall duration was cut roughly by a factor of 25, so now this whole task completes in about two minutes vs. one hour before. Actually, now it is even fun to watch how SAS logs and output data sets are being updated in real time.

What is more, this script-centric approach can be used for running not just SAS processes, but non-SAS, open source and/or hybrid processes as well. This makes it a powerful amplifier and integrator for heterogeneous software applications development.

SAS Consulting Services

The solution presented in this post is a stripped-down version of the original production quality solution. This better serves our educational objective of communicating the key concepts and coding techniques. If you believe your organization’s computational powers are underutilized and may benefit from a SAS Consulting Services engagement, please reach out to us through your SAS representative, and we will be happy to help.

Additional resources

Thoughts? Comments?

Do you find this post useful? Do you have processes that may benefit from parallelization? Please share with us below.

Using shell scripts for massively parallel processing was published on SAS Users.

4月 012021

Uncertainty Principle blackboard

If I were to say that we live in uncertain times, that would probably be an understatement. Therefore, I won’t say that. Oops, I already did. Or did I?

For centuries, people around the world have been busy scratching their heads in search of a meaningful answer to Shakespeare’s profoundly elementary question: “To be or not to be?”

Have we succeeded? Sure. And in pursuit of even further greatness, we have progressed beyond the simple binary choice. Thanks to human ingenuity, it is now possible to have it all: to be and not to be.

But doesn’t this contradict human logic? Not at all, according to the Heisenberg uncertainty principle – a cornerstone of quantum mechanics asserting a fundamental limit to the certainty of knowledge.

According to the uncertainty principle, it is not possible to determine both the momentum and position of particles (bosons, electrons, quarks, etc.) simultaneously. Here is the famous formula:

Δx = uncertainty in position.
Δp = uncertainty in momentum.
h = Planck’s constant (a rare and precious number equal to 6.62607015×10−34 representing how much the energy of a photon increases, when the frequency of its electromagnetic wave increases by 1).
4π = π π π π (4 pi’s; no mathematical formula of any scientific significance can do without at least one of them!)

In addition, every particle or quantum entity may be defined as either a particle or a wave depending on how you feel about it according to the wave-particle duality principle. But let’s not let the dual meaning inconvenience us. Let’s just call them matters, or things for simplicity.

Then we can formulate the uncertainty principle in plain and clear terms:

Since it is impossible to know whether the position of a thing is X or not X, then that thing can be in position X and not be in position X simultaneously. Thus “to be and not to be”.


There is an abundance of examples of the uncertainty principle in SAS software. Let’s consider several of them.

History of the present and present of the history

Some of you may remember SAS version 7.0. It’s remarkable in a way that it was the shortest-lived SAS version that lasted roughly one year. It was released in October 1998 and was replaced by SAS 8.0 in November 1999. There were no 7.1 or 7.2 sub-versions, only 7.0.

But (and this is a big BUT), have you noticed that even today the latest SAS products (9.4 and Viya) use the following 9
Physical Name: c:\temp

Notice how it’s SAS Engine V9, but SAS datasets created with it have .sas7bdat extensions.

Where do you think that digit “7” came from? Obviously, even almost two decades after version 7.0’s demise it is still alive and kicking. How can you explain that other than by the uncertainty principle: “it is while it is not”!

Transience and permanence

Let’s take another example. How long have you known the fact that in order to create a permanent SAS data set you need to specify its name as a two-level name, e.g. LIBREF.DATASETNAME, while for temporary data sets you can specify a one-level name, e.g. DATASETNAME, or you can use a two-level name where the first level is WORK to explicitly signify the temporary library. Now, equipped with that “settled science” knowledge, what do you think the following code will create, a temporary or a permanent data set?

options user='c:\temp';
data MYDATA;
   x = 22371;

Just run this code and check your c:\temp folder to make sure that data set MYDATA is permanent. Credit for this shortcut goes to the

options user='c:\temp';
   x = 22371;

SAS Log will show:
NOTE: The data set USER.DATA1 has 1 observations and 1 variables.

Isn’t an ultimate proof of the “to be and not to be” principle (sponsored by create a data set by defining its physical pathname without even relying on SAS data set names, whether one or two-level:

data "c:\temp\aaa";
   x = 22371;
   format x date9.;

This code runs perfectly fine, creating a SAS data set as a file named aaa.sas7bdat in the c:\temp folder.

And I am not even talking  about the Uncertainty principle: Final Exam

And now, ladies and gentlemen, you will have to pass your final exam to receive an official April Fools diploma from SAS University.

Problem to solve

You know that every SAS data step creates automatic variables, _N_ and _ERROR_, which are available during the data step execution. Is it possible to save those automatic variables on the output data set?

In other words, will the following code create 3 variables on the output data set ABC?

data ABC (keep=MODEL _N_ _ERROR_);
   set SASHELP.CARS(keep=MODEL);

If you answered “No” you get 1 credit. If you answered “Yes” you get 0 credit. But that’s only if you answered the second question (I assume you noticed that I asked two questions in a row). If your “Yes”/ ”No” answer relates to the first question your credits are in reverse.

Bonus for creativity

However, if you not only answered “Yes” to the first question, but also provided a “how-to” code example, you get a bonus in the amount of 10 credits. Here is your bonus for creativity:

data BBC;
   set SASHELP.CARS(keep=MODEL);
   x = _n_;
   e = _error_;
   rename x=_n_ e=_error_;

You still have to run this code to make sure it creates data set BBC with 3 variables: MODEL, _N_, and _ERROR_ in order to get your 10 credits vested.

Problem solved = problem created

And lastly, the final curiosity test and exercise where you find out about SAS’ no-nonsense solution in the face of uncertainty. What happens in the following data step when the SAS-created automatic data step variables, _N_ and _ERRROR_, collide with the same-name variables brought in by the previously created BBC data set?

data CBC;
   set BBC;

After you complete this test/exercise and find out the answer, you can grab your diploma below and proudly brag about it and display it anywhere.

SAS Institute diploma

WAIT! Before you leave, please do not forget to provide your answers, questions, code examples, and comments below.

More April Fools’ Day SAS articles

April 1, 2020: Theory of relativity in SAS programming
April 1, 2019: Dividing by zero with SAS
April 1, 2018: SAS discovers a new planet in the Solar System
April 1, 2017: SAS code to prove Fermat's Last Theorem

To be and not to be – the uncertainty principle in SAS was published on SAS Users.

3月 252021

French leave, English style leave, Irish goodbyeIn many SAS applications, there is a need to conditionally stop SAS code execution and gracefully (without generating an ERROR or a WARNING) terminate SAS session when no further processing is required. For example, your program processes large data and flags certain transactions as suspicious. If any suspicious transactions are found, then you continue further processing to gather more information about those transactions. However, if no transactions were flagged, you just want to stop your SAS job and augment the SAS log with a nice NOTE (not ERROR or WARNING), for example:

NOTE: No suspicious transactions are found. Further processing canceled. Exiting program execution.

Graceful termination techniques described in this post primarily apply to batch processing scenarios when SAS programs run unattended without human intervention. However, development of these programs is usually done in interactive SAS sessions, and we need to make sure SAS log is captured before our interactive application is “terminated”. Therefore, before we proceed reviewing and experimenting with SAS termination techniques, let’s make arrangements for capturing the SAS log.

Capturing SAS log

When you run a SAS program in batch you would usually submit it using OS command. For example, in UNIX/Linux you may submit or place in a shell script the following command:

sas /code/proj1/job1.sas -log /code/proj1/job1.log

The log file name is specified in the command itself (-log /code/proj1/job1.log), and it will record our “goodbye NOTE” generated before SAS session is terminated.

However, when you run/debug your program in an interactive application, e.g. Enterprise Guide, the SAS log is captured in the Log Window. When SAS terminates its session, it will not only terminate your program execution, it will also terminate the application itself. That means your Enterprise Guide will close, along with its Log Window leaving you with no Log to see and inspect.

To capture SAS log to a file while debugging a SAS program containing session termination feature in interactive environment/application you can use

/* Beginning of SAS program */
proc printto log='C:\PROJECT1\program1.log';
/* End of SAS program */
proc printto; run;

In this code ABORT Statement is an executable statement that can be used as part of IF-THEN/ELSE conditional processing in a DATA step. Its action, however, extends beyond the DATA step as it not only stops executing the current DATA step, but also stops executing SAS session.

There are several flavors of the ABORT statement in SAS 9.4 – ABORT, ABORT ABEND, ABORT CANCEL, ABORT RETURN, and ABORT <number>. All of them are useful when something goes bad in your program (e.g. database library is not available at the time of run, or a data set is locked preventing you from writing to it, etc.), and you need to kill your program to avoid an even bigger snafu. All such situations are real, and ABORT statement handles them properly – stopping further program execution, killing SAS session, and generating an ERROR message in the SAS log.

But that ERROR message in the SAS log is what effectively disqualifies ABORT statements from graceful termination status. Think about it: “no suspicious transactions” is an occasion to celebrate, not a reason to cry an ERROR. Besides, in many mission-critical production-quality applications having an ERROR or even a WARNING in the SAS log is not an option.

Fortunately, for SAS® Viya users, there is a new global statement, it takes effect as soon as it is encountered in a SAS program. It can be placed anywhere in a SAS program, except where only executable statements are allowed. For example, if you place it as part of the IF-THEN/ELSE statement you get a syntax ERROR. Try running this code:

data _null_;
   if 1=1 then endsas;

SAS log will show a syntax error:

2    data _null_;
3       if 1=1 then endsas;
ERROR 180-322: Statement is not valid or it is used out of proper order.

However, if you place it in a conditionally executed DO-block it will not generate a syntax ERROR, but it will not produce what we wanted either because of the following.

First, it will execute even when the condition if FALSE. Second, since it executes inside the DO-block, it will end SAS data step and SAS session right there, without even giving DO-block a chance of completing its compilation, thus generating an ERROR. Here is the code illustration:

data _null_;
   if 0 then
      put 'Nooo!';

If you dare to run this code, here is what you will see in the SAS log after your SAS session gets killed:

2    data _null_;
3       if 0 then
4       do;
5          put 'Nooo!';
6          endsas;
ERROR 117-185: There was 1 unclosed DO block.

No, it is not what we are after.

ENDSAS statement – data step solution

Using coding technique described in my previous post How to conditionally execute SAS global statements, we can make ENDSAS to be conditionally generated within data step and executed after the data step.

Suppose we have a data set SUSPICIOUS_CASES which may have either zero or some positive number of observations. We want to stop further processing and terminate SAS session in case it has 0 observations.

Here is how we can achieve this:

data _null_;
   set SUSPICIOUS_CASES nobs=n;
   if n=0 then call execute('endsas;');

Here we conditionally (if n=0) invoke CALL EXECUTE routine which un-quotes its character argument and pushes it outside the data step boundaries, thus generating the following code after RUN statement (as shown in the SAS log):

NOTE: CALL EXECUTE generated line.
1   + endsas;

Thus, we conditionally generated global ENDSAS statement and placed it after the data step. This global statement will terminate SAS session without a fuss; no ERROR, no WARNING, and even no NOTE.

If you want it to be not so “silent goodbye”, you can add some informative NOTES using PUT statement executed under the same condition as CALL EXECUTE (we combine them in a DO-block):

data _null_;
   set SUSPICIOUS_CASES nobs=n;
   if n=0 then
      put 'NOTE: No suspicious cases were found. Further processing is terminated.';
      call execute('endsas;');

This code will conditionally output NOTE to the SAS log during data step. Obviously, you can generate any number of NOTE lines making your exit more verbose. We can place PUT statement either before or after CALL EXECUTE within the DO-block, because it will be executed within the data step, while generated ENDSAS statement will be executed after the data step.

ENDSAS statement – SAS macro solution

Another way of conditionally generating SASEND statement is by using

%if 1 %then
   %put NOTE: Ending SAS session gracefully.;

SAS log will show:

2    %if 1 %then
3    %do;
4       %put NOTE: Ending SAS session gracefully.;
NOTE: Ending SAS session gracefully.
5       endsas;
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414

Additional resources

Thoughts? Comments?

Do you find this blog post useful? How do you handle graceful termination of your SAS programs? Please share with us below.

How to conditionally stop SAS code execution and gracefully terminate SAS session was published on SAS Users.

3月 162021

SAS global statementsSAS IF-THEN/ELSE statement that executes DATA step statements depending on specified conditions:

IF expression THEN executable-statement1;
<ELSE executable-statement2;>

Try sticking it in there and SAS will slap you with an ERROR:

data _null_;
   set SASHELP.CARS nobs=n;
   if n=0 then libname outlib 'c:\temp';

SAS log will show:

3       if n=0 then libname outlib 'c:\temp';
ERROR 180-322: Statement is not valid or it is used out of proper order.

But global statements’ “not executable” status only means that they cannot be executed as part of a DATA step execution. Otherwise, “they take effect” (in my mind that equates to “they execute”) right after the compilation phase but before DATA step executes (or processes) its data reads, writes, logic and iterations.

Here is another illustration. Let’s get a little creative and tack a LIBNAME global statement within conditionally executed DO-group of the IF-THEN statement:

   set SASHELP.CARS nobs=n;
   if n=0 then
      libname OUTLIB 'c:\temp';
In this case, SAS log will show:
NOTE: Libref OUTLIB was successfully assigned as follows:
      Engine:        V9
      Physical Name: c:\temp
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set OUTLIB.CARS has 428 observations and 15 variables.

As you can see, not only our LIBNAME statement “executed” (or “took effect”) despite the IF-THEN condition was FALSE, it successfully assigned the OUTLIB library and applied it to the data OUTLIB.CARS; statement that appears earlier in the code. That is because the LIBNAME global statement took effect (executed) right after the DATA step compilation before its execution.

For the same reason, you can place global statement TITLE either in open code before PROC that produces output with a title or within that PROC. In the first case, the stand-alone TITLE statement is compiled on its own and immediately executed thus setting the title for the PROCs that follow. In the latter case, it is compiled with the PROC step, then immediately executed before PROC step’s execution.

Now, when we have a solid grasp of the global statements timing habits, let’s look at the coding techniques allowing us to take full control of when and whether global statements take effect (executed).

Macro language to conditionally execute SAS global statements


%let dsname = SASHELP.CARS;
/*%let dsname = SASHELP.CLASS;*/
%let name = %scan(&dsname,2);
%if (&name eq CARS) or (&name eq CLASS) %then
   options DLCREATEDIR;
   libname outlib "c:\temp\&name";
   libname outlib "c:\temp";
data OUTLIB.&name;
   set &dsname;

In this code, if name is either CARS or CLASS the following global statements will be generated and passed on to the SAS compiler:

   options DLCREATEDIR;
   libname outlib "c:\temp\&name";

This will create a directory c:\temp\&name (if it does not exist) and assign libref OUTLIB to that directory.

Otherwise, the following global statement will be generated and passed on to the SAS compiler:

   libname outlib "c:\temp";

The DATA step then creates data set OUTLIB.&name in the corresponding dynamically assigned library. Using this technique, you can conditionally generate global statements for SAS system options, librefs, filerefs, titles, footnotes, etc. SAS compiler will pick up those generated global statements and execute (activate, put in effect) them.

CALL EXECUTE to conditionally execute SAS global statements

Sometimes, it is necessary to conditionally execute global statements based on values contained in data, whether in raw data or SAS data sets. Such a data-driven approach can be easily implemented using CALL EXECUTE routine in a DATA step.

data _null_;
   by MAKE;
   if first.MAKE then
      call execute('title "'||trim(MAKE)||' models";');
      call execute('proc print noobs data=SASHELP.CARS(where=(MAKE="'||trim(MAKE)||'"));');
      call execute('   var MAKE MODEL TYPE;');
      call execute('run;');

In this code, for every block of unique MAKE values (identified by first.MAKE) we have CALL EXECUTE generating lines of SAS code and pushing them outside the DATA step boundary where they compile and execute. The code snippets for TITLE and WHERE clause are data-driven and generated dynamically. The SAS log will show a series of the generated statements:

NOTE: CALL EXECUTE generated line.
1   + title "Acura models";
2   + proc print noobs data=SASHELP.CARS(where=(MAKE="Acura"));
3   +    var MAKE MODEL TYPE;
4   + run;
5   + title "Audi models";
6   + proc print noobs data=SASHELP.CARS(where=(MAKE="Audi"));
7   +    var MAKE MODEL TYPE;
8   + run;

. . . and so forth.

In this implementation, global statement TITLE is prepared (“pre-cooked”) conditionally (if first.MAKE is TRUE) within the DATA step in a form of a character value. It’s still not a global statement until CALL EXECUTE pushes it out of the DATA step. There it becomes a global statement as part of SAS code stream. There it gets compiled and executed, setting a nice data-driven title for the PROC PRINT output (individually for each Make):

PROC PRINT outputs with dynamically generated titles

Additional resources

Your thoughts?

Have you found this blog post useful? Do you have any questions? Please feel free to ask and share your thoughts and feedback in the comments section below.

How to conditionally execute SAS global statements was published on SAS Users.

2月 222021

Removing a piece from character string In my previous post, we addressed the problem of inserting substrings into SAS character strings. In this post we will solve a reverse problem of deleting substrings from SAS strings.

These two complementary tasks are commonly used for character data manipulation during data cleansing and preparation to transform data to a shape suitable for analysis, text mining, reporting, modeling and decision making.

As in the previous case of substring insertion, we will cover substring deletion for both, character variables and macro variables as both data objects are strings.

The following diagram illustrates what we are going to achieve by deleting a substring from a string:

Removing a substring from SAS string illustration

Have you noticed a logical paradox? We take away a “pieceof” cake and get the whole thing as result! 😊

Now, let’s get serious.

Deleting all instances of a substring from a character variable

Let’s suppose we have a variable STR whose values are sprinkled with some undesirable substring ‘<br>’ which we inherited from some HTML code where tag <br> denotes a line break. For our purposes, we want to remove all instances of those pesky <br>’s. First, let’s create a source data set imitating the described “contaminated” data:

data HAVE;
   infile datalines truncover;
   input STR $100.;
Some strings<br> have unwanted sub<br>strings in them<br>
<br>A s<br>entence must not be cont<br>aminated with unwanted subs<br>trings
Several line<br> breaks<br> are inserted here<br><br><br>
<br>Resulting st<br>ring must be n<br>eat and f<br>ree from un<br>desirable substrings
Ugly unwanted substrings<br><br> must <br>be<br> removed
<br>Let's remove them <br>using S<br>A<br>S language
Ex<br>periment is a<br>bout to b<br>egin
<br>Simpli<br>city may sur<br>prise you<br><br>

This DATA step creates WORK.HAVE data set that looks pretty ugly and is hardly usable:
Source data to be cleansed
The following code, however, cleans it up removing all those unwanted substrings ‘<br>’:

data WANT (keep=NEW_STR);
   length NEW_STR $100;
   SUB = '<br>';
   set HAVE;
   NEW_STR = transtrn(STR,'<br>',trimn(''));

After this code runs, the data set WANT will look totally clean and usable:
Cleaned data

Code highlights

  • We use .

The TRANSTRN function is similar to TRANWRD function which replaces all occurrences of a substring in a character string. While TRANWRD uses a single blank when the replacement string has a length of zero, TRANSTRN does allow the replacement string to have a length of zero which essentially means removing.

  • TRIM() function which removes trailing blanks from a character string and returns one blank if the string is missing. However, when it comes to removing (which is essentially replacement with zero length substring) the ability of TRIMN function to return a zero-length string makes all the difference.

Deleting all instances of a substring from a SAS macro variable

For macro variables, I can see two distinct methods of removing all occurrences of undesirable substring.

Method 1: Using SAS data step

Here is a code example:

%let STR = Some strings<br> have unwanted sub<br>strings in them<br>;
%let SUB = <br>;
data _null_;
   NEW_STR = transtrn("&STR","&SUB",trimn(''));
   call symputx('NEW',NEW_STR);
%put &=STR;
%put &=NEW;

In this code, we stick our macro variable value &STR in double quotes in the transtrn() function as the first argument (source). The macro variable value &SUB, also double quoted, is placed as a second argument. After variable NEW_STR is produced free from the &SUB substrings, we create a macro variable NEW using

%let STR = Some strings<br> have unwanted sub<br>strings in them<br>;
%let SUB = <br>;
%let NEW = %sysfunc(transtrn(&STR,&SUB,%sysfunc(trimn(%str()))));
%put &=STR;
%put &=NEW;

Deleting selected instance of a substring from a character variable

In many cases we need to remove not all substring instances form a string, but rather a specific occurrence of a substring. For example, in the following sentence (which is a quote by Albert Einstein) “I believe in intuitions and inspirations. I sometimes feel that I am right. I sometimes do not know that I am.” the second word “sometimes” was added by mistake. It needs to be removed. Here is a code example presenting two solutions of how such a deletion can be done:

data A;
   length STR STR1 STR2 $250;
   STR = 'I believe in intuitions and inspirations. I sometimes feel that I am right. I sometimes do not know that I am.';
   SUB = 'sometimes';
   STR_LEN = length(STR);
   SUB_LEN = length(SUB);
   POS = find(STR,SUB,-STR_LEN);
   STR1 = catx(' ', substr(STR,1,POS-1), substr(STR,POS+SUB_LEN)); /* solution 1 */
   STR2 = kupdate(STR,POS,SUB_LEN+1);                              /* solution 2 */
   put STR1= / STR2=;

The code will produce two correct identical values of this quote in the SAS log (notice, that the second instance of word “sometimes” is gone):

STR1=I believe in intuitions and inspirations. I sometimes feel that I am right. I do not know that I am.
STR2=I believe in intuitions and inspirations. I sometimes feel that I am right. I do not know that I am.

Code highlights

Solution 1

This is the most traditional solution that cuts out two pieces of the string – before and after the substring being deleted – and then concatenates them together thus removing that substring:

  • substr(STR,1,POS-1) extracts the first part of the source string STR before the substring to be deleted: from position 1 to position POS-1.
  • substr(STR,POS+SUB_LEN) extracts the second part of the source string STR after the substring to be deleted: from position POS+SUB_LEN till the end of STR value (since the third argument, length, is not specified).
  • Solution 2

    Finding n-th instance of a substring within a string .

Deleting selected instance of a substring from a SAS macro variable

Here is a code example of how to solve the same problem as it relates to SAS macro variables. For brevity, we provide just one solution using %sysfunc and KUPDATE() function:

%let STR = I believe in intuitions and inspirations. I sometimes feel that I am right. I sometimes do not know that I am.;
%let SUB = sometimes;
%let POS = %sysfunc(find(&STR,&SUB,-%length(&STR)));
%let STR2 = %sysfunc(kupdate(&STR,&POS,%eval(%length(&SUB)+1)));
%put "&STR2";

This should produce the following corrected Einstein’s quote in the SAS log:

"I believe in intuitions and inspirations. I sometimes feel that I am right. I do not know that I am."

Additional Resources for SAS character strings processing

Your thoughts?

Have you found this blog post useful? Please share your thoughts and feedback in the comments section below.

Deleting a substring from a SAS string was published on SAS Users.

2月 162021

Insert a piece into a stringSAS provides an extensive set of tools for data cleansing and preparation – transforming data to a shape suitable for analysis, text mining, reporting, modeling and ultimately decision making.

In this post we will cover one of the common tasks of character data manipulation – inserting a substring into a SAS character string.

A diagram below illustrates what we are going to achieve:

Illustration for inserting a substring into a string

SAS character strings come in two different incarnations: character variables and macro variables. Since these two are quite different SAS language objects, let’s cover them one by one separately.

Inserting a substring into a character variable

Here is our task: we have a SAS character variable (string) and we want to insert in it a value of another character variable (substring) starting at a particular specified position.

Let’s say we have a string BASE in which we want to insert a COUNTRY name right before word "stays" to make different variation of the resultant phrase. Here is an example of how this can be easily done:

   length COUNTRY $20;
   input COUNTRY;
   BASE = 'The rain in stays mainly in the plain';
   INSPOS = find(BASE,'stays');
   length PHRASE $50;
   PHRASE = catx(' ',substr(BASE,1,INSPOS-1),COUNTRY,substr(BASE,INSPOS));

This code dynamically creates variable PHRASE out of values of variable BASE and the values of variable COUNTRY, thus making it data-driven.

After this code runs, the data set NEW will look like this:

Results after inserting a substring into a character string

Here are the code highlights:

  • maximum length of varying-length character variables is 536,870,911 characters (UTF-8 encoding).

    Inserting a substring into a SAS macro variable

    Let’s solve a similar task, but now instead of SAS variables we will operate with SAS macro variables, since they are strings too.

    Here is our problem to solve: we have a SAS macro variable (string) and we want to insert in it a value of another macro variable (substring) starting at a particular specified position.

    Let’s say we have a macro variable BASE with value of The rain in stays mainly in the plain in which we want to insert a country name defined by macro variable COUNTRY with value of Spain right before word stays. Here is an example of how this can be done:

    %let BASE = The rain in stays mainly in the plain;
    %let COUNTRY = Spain;
    %let W = stays;
    %let INSPOS = %index(&BASE,&W);
    %let PHRASE = %substr(&BASE,1,%eval(&INSPOS-1))&COUNTRY %substr(&BASE,&INSPOS);
    %put ***&PHRASE***;

    This code will insert the country name in the appropriate place within the BASE macro variable which will be printed in the SAS log by %put statement:

    ***The rain in Spain stays mainly in the plain***

    Here are the code highlights:

    • %substr() macro function to extract two parts of its first argument (&BASE) - before and after insertion:
      • %substr(&BASE,1,%eval(&INSPOS-1))captures the first part of &BASE (before insertion): substring of &BASE starting from the position 1 with a length of %eval(&INSPOS-1).
      • %substr(&BASE,&INSPOS) captures the second part of &BASE (after insertion): substring of &BASE starting from the position &INSPOS till the end of &BASE (since the third argument is not specified).
    • In case of macro variables, we don’t need any concatenation functions – we just list the component pieces of the macro variable value in a proper order with desired separators (blanks in this case).

    NOTE: Unlike for SAS variables, you don’t need to assign the length of SAS macro variables which are automatically defined by their assigned values. The maximum length of SAS macro variables is 65,534 bytes.

    Inserting multiple instances of a substring into a SAS character string

    Sometimes you need to insert a substring into several places (positions p1, p2, …, pn) of a character string. In this case you can use the above strategy repeatedly or iteratively with one little caveat: start inserting from the highest position and moving backwards to the lowest position. This will preserve your pre-determined positions because positions are counted from left to right and inserting a substring at a higher position won’t change the lower position number. Otherwise, after insertion of a substring into lower position, all your higher positions will shift by the length of the inserted substring.

    Additional Resources for SAS character strings processing

    Your thoughts?

    Have you found this blog post useful? Please share your thoughts and feedback in the comments section below.

    Inserting a substring into a SAS string was published on SAS Users.

  • 1月 272021

    SASensei logoSAS offers myriad ways to level up your SAS skills (scroll to the bottom to see a list of SAS-provided learning paths and channels). In this post, I introduce you to SASensei, an independent, third-party online SAS learning resource that I enjoy a lot.

    Learning: dull or fun?

    Learning is not always associated with fun. Sometimes it feels difficult and exhausting. New concepts often contradict our prior knowledge and experience, compelling us to rethink, adjust, change and adapt to new paradigms.

    Learning new ideas, skills and technologies can be intimidating, challenging and demanding. While learning, you are stretching out of your comfort zone. But that feeling is only transient. As a matter of attitude, learning is not about pushing yourself out of your comfort zone, it’s about expanding your comfort zone. And that is long lasting. The more you learn, the more comfortable and self-confident you become.

    Learning does not have to be tedious. Look at pre-school kids. They learn basic life skills like walking (rolling, crawling), talking (in one or more languages), asking questions (a lot) – all without taking classes, just through their natural curiosity and ... playing games.

    What is SASensei? Gamified SAS learning

    When I first discovered the SASensei online SAS learning game/application I was pleasantly surprised by its non-traditional approach to learning such a serious and well-established platform as SAS.

    As stated on its website, “Sasensei is a question based learning system. You must demonstrate your command of SAS® to earn Tokens - which should be wisely invested, to enable you to unlock new levels within the game...”

    The following screenshot shows the main page of the SASensei website that displays a dashboard of the top players (they call it leaderboard). You can filter it geographically - by Country, Continent, or World, as well as by the timeline – by Past Month, Past Year, or All Time.

    SASensei leaderboard

    Privacy or prominence

    Users have full control of their privacy or prominence. As you can see in the screenshot above, registered users are displayed by their screen names. This allows the users to either remain anonymous by selecting some fictitious obscure screen name or use their real name. Users can change their screen name at any time.

    Rules of the game

    In this blog post I provide just an overview of the main functionality and features of the SASensei learning platform. For detailed rules of the game, see SASensei Documentation.

    Play and learn

    Users are offered a variety of learning activities:

    • Viewing, reviewing and submitting SAS-learning flashcards;
    • Playing, reviewing and submitting questions by different SAS-related topics;
    • Taking and creating public, private, multi-player and custom quizzes;
    • Providing feedback on questions and flashcards by voting and commenting.

    Users can challenge themselves by delving into different topics. Your successes and failures will provide you an honest and objective estimation of your SAS strengths as well as weaknesses. A healthy competition with other users encourages you to learn more and hone your SAS skills. When you fail a question, you can review the explanation of the correct answer and thus learn why you failed and acquire new knowledge, tips and tricks quickly and efficiently.

    Invest, score, win and build a reputation

    To play you will need to earn and spend tokens which are essentially the game’s currency. To motivate you further, you also earn reputation points, which is your ultimate score, a level of achievement in demonstrating SAS skills. Your reputation score is prominently displayed in your public profile. As you progress in the game and your reputation grows, additional functionality unlocks and becomes available to you. Your reputation score determines your SASensei standing level which is derived from those used in martial arts:

    Sasensei title Sasamurai title

    • White Belt (new players)
    • Yellow Belt (reputation ≥ 50)
    • Green Belt (reputation ≥ 100)
    • Black Belt (reputation ≥ 200)
    • Sasamurai (reputation ≥ 500)
    • Assassin (reputation ≥ 1000)
    • Sasensei (reputation ≥ 5000)

    Sample SASensei question

    When you play a question, you select a topic, and then you are presented with a randomly selected multiple-choice question of a specified time limit (30, 60, 90 or 120 seconds). Here is a sample of such question:


    What is wrong with the following LIBNAME statement?
    libname fruits (apples oranges tomatoes);


    • Incorrect syntax
    • You cannot mix apples and oranges in LIBNAME statement
    • Nothing is wrong, valid LIBNAME statement
    • Tomatoes are not fruits, therefore the statement is not correct

    Correct answer:

    Nothing is wrong, valid LIBNAME statement


    According to Combine and conquer with SAS for examples of usage.

    Try tackling a question on your own in the SASensei environment to get real life experience: Sample Question.

    Take a SASensei sample quiz

    There are various quizzes available at SASensei: public quizzes, multiplayer quiz games, private quizzes (tests) for students.

    A public quiz contains 12 questions with a total time cap of 12 minutes, and costs eight tokens to play. You can choose a single topic (sas statements, sas macro, procedures, options, etc.), and if you pass (75% or more) you get 12 tokens back, plus 20 point to your reputation. If you get 100%, you get 30 reputation points plus Top Student badge. A count of passed sessions (by topic) is displayed on your public profile.

    QuizAlthough public quizzes are unlocked at the SASamurai level, for the readers of this blog, I have created a special custom quiz sample so you can experience it firsthand right here, right now. Just click on this link, Sample Quiz, register, and enjoy your ride.

    See you at the top of the SASensei dashboard!


    Big THANKS to Allan Bowe (United Kingdom) – SAS innovator and entrepreneur who created and founded SASensei learning platform.

    Other SAS learning resources

    Game on! SASensei: a fun way to learn SAS was published on SAS Users.

    1月 132021

    Running SAS programs in parallel reduces run time

    As earth completes its routine annual circle around the sun and a new (and hopefully better) year kicks in, it is a perfect occasion to reflect on the idiosyncrasy of time.

    While it is customary to think that 3+2=5, it is only true in sequential world. In parallel world, however, 3+2=3. Think about it: if you have two SAS programs one of which runs 3 hours, and the second one runs 2 hours, their total duration will be 5 hours if you run them one after another sequentially, but it will take only 3 hours if you run them simultaneously, in parallel.

    I am sure you remember those “filling up a swimming pool” math problems from elementary school. They clearly and convincingly demonstrate that two pipes will fill up a swimming pool faster than one. That’s the power of running water in parallel.

    The same principle of parallel processing (or parallel computing) is applicable to SAS programs (or non-SAS programs) by running their different independent pieces in separate SAS sessions at the same time (in parallel).  Divide and conquer.

    You might be surprised at how easily this can be done, and at the same time how powerful it is. Let’s take a look.


    MP CONNECT) parallel processing functionality was added to SAS/CONNECT enabling you to execute multiple SAS sessions asynchronously. When a remote SAS session kicks off asynchronously, a portion of your SAS program is sent to the server session for execution and control is immediately returned to the client session. The client session can continue with its own processing or spawn one or more additional asynchronous remote server sessions.

    Running programs in parallel on a single machine

    Sometimes, what comes across as new is just well forgotten old. They used to be Central Processing Units (CPU), but now they are called just processors. Nowadays, practically every single computer is a “multi-machine” (or to be precise “multi-processor”) device. Even your laptop. Just open Task Manager (Ctrl-Alt-Delete), click on the Performance tab and you will see how many physical processors (or cores) and logical processors your laptop has:

    Parallel processing on a single machine

    That means that this laptop can run eight independent SAS processes (sessions) at the same time. All you need to do is to say nicely “Dear Mr. & Mrs. SAS/CONNECT, my SAS program consists of several independent pieces. Would you please run each piece in its own SAS session, and run them all at the same time?” And believe me, SAS/CONNECT does not care how many logical processors you have, whether your logical processors are far away from each other “remote machines” or they are situated in a single laptop or even in a single chip.

    Here is how you communicate your request to SAS/CONNECT in SAS language.

    Spawning multiple SAS sessions using MP Connect

    Suppose you have a SAS code that consists of several pieces – DATA or PROC steps that are independent of each other, i.e. they do not require to be run in a specific sequence. For example, each of the two pieces generates its own data set.

    Then we can create these two data sets in two separate “remote” SAS sessions that run in parallel. Here is how you do this.  (For illustration purposes, I just create two dummy data sets.)

    options sascmd="sas";
    /* Current datetime */
    %let _start_dt = %sysfunc(datetime());
    /* Prosess 1 */
    signon task1;
    rsubmit task1 wait=no;
       libname SASDL 'C:\temp';
       data SASDL.DATA_A (keep=str);
          length str $1000;
          do i=1 to 1150000;
             str = '';
             do j=1 to 1000;
                str = cats(str,'A');
    /* Process 2 */
    signon task2;
    rsubmit task2 wait=no;
       libname SASDL 'C:\temp';
       data SASDL.DATA_B (keep=str);
          length str $1000;
          do i=1 to 750000;
             str = '';
             do j=1 to 1000;
                str = cats(str,'B');
    waitfor _all_;
    signoff _all_;
    /* Print total duration */
    data _null_;
       dur = datetime() - &_start_dt;
       put 30*'-' / ' TOTAL DURATION:' dur time13.2 / 30*'-';

    In this code, the key elements are:

    SIGNON Statement - initiates a connection between a client session and a server session.

    ENDRSUBMIT statement - marks the end of a block of statements that a client session submits to a server session for execution.

    SIGNOFF Statement - ends the connection between a client session and a server session.

    Parallel processing vs. threaded processing

    There is a distinction between parallel processing described above and threaded processing (aka multithreading). Parallel processing is achieved by running several independent SAS sessions, each processing its own unit of SAS code.

    Threaded processing, on the other hand, is achieved by developing special algorithms and implementing executable codes that run on multiple processors (threads) within the same SAS session. Amdahl's law, which provides theoretical background and estimation of potential time saving achievable by parallel computing on multiple processors.

    Passing information to and from “remote” SAS sessions

    Besides passing pieces of SAS code from client sessions to server sessions, MP CONNECT allows you to pass some other SAS objects.

    Passing data library definitions

    For example, if you have a data library defined in your client session, you may pass that library definition on to multiple server sessions without re-defining them in each server session.

    Let’s say you have two data libraries defined in your client session:

    libname SRCLIB oracle user=myusr1 password=mypwd1 path=mysrv1;
    libname TGTLIB '/sas/data/datastore1';

    In order to make these data libraries available in the remote session all you need is to add

    rsubmit task1 wait=no inheritlib=(SRCLIB TGTLIB);

    This will allow libraries that are defined in the client session to be inherited by and available in the server session. As an option, each client libref can be associated with a libref that is named differently in the server session:

    rsubmit task1 wait=no inheritlib=(SRCLIB=NEWSRC TGTLIB=NEWTGT);

    Passing macro variables from client to server session

    options sascmd="sas";
    %let run_dt = %sysfunc(datetime());
    signon task1;
    %syslput rem_run_dt=&run_dt / remote=task1;
    rsubmit task1 wait=no;
       %put &=rem_run_dt;
    waitfor task1;
    signoff task1;

    Passing macro variables from server to client session


  • %SYSRPUT_USER_ </LIKE=‘character-string’>;
  • (/LIKE=<‘character-string’ >specifies a subset of macro variables whose names match a user-specified character sequence, or pattern.)

    Here is a code example that passes two macro variables, rem_start and rem_year from the remote session and outputs them to the SAS log in the client session:

    options sascmd="sas";
    signon task1;
    rsubmit task1 wait=no;
       %let start_dt = %sysfunc(datetime());
       %sysrput rem_start=&start_dt;
       %sysrput rem_year=2021;
    waitfor task1;
    signoff task1;
    %put &=rem_start &=rem_year;


    SAS’ Multi-Process Connect is a simple and efficient tool enabling parallel execution of independent programming units. Compared to sequential processing of time-intensive programs, it allows to substantially reduce overall duration of your program execution.

    Additional resources

    Running SAS programs in parallel using SAS/CONNECT® was published on SAS Users.

    11月 042020

    Removing duplicate charactersIn this blog post we are going to tackle a data cleansing task of removing unwanted repeated characters in SAS character variables.

    Character repetition can stem from various stages of data life cycle: from data collection, to data transmission, to data transformation. It can be accidental or intentional by design. It can be sporadic or consistent. In either case, it needs to be addressed by robust data cleansing processes to ensure adequate data quality that is imperative for the data usability.

    Character repetition examples

    Example 1. Data entry, especially manual data entry, can be a high-risk factor for accidental character duplication. Have you ever pressed a key on your computer keyboard for a bit longer than intended, so it started automatically typing multiple characters???????????????

    Keyboard properties adjustment

     Tip: You can adjust your Keyboard Properties to control “Repeat delay” and “Repeat rate” settings (on Windows computer, search for “Keyboard” and click on Keyboard in Control Panel).

    Example 2. Recently, I had to deal with the data that contained multiple consecutive double quotation marks all over the character string values. Even though we don’t know the exact cause of it, still for each of these duplicated quotation marks occurrences we needed to replace them with a single quotation mark.

    Removing repeated blanks

    There is a very useful Removing unwanted characters from text strings by Amadeus Software we developed a prototype using

    data D;
       c = ','; *<- character to un-duplicate;
       cc = c||c; *<- double character;
       string = 'Many,,,,,, commas,,,,, in,,, this,, sentence.,'; *<- source string;
       put 'BEFORE:' string=; *<- output initial string;
       do while (find(string,cc)); *<- loop through while there are doubles;
          string = tranwrd(string,cc,c); *<- replace double with a single character;
       put 'AFTER: ' string=; *<- output unduplicated string;

    This code will produce the following in the SAS log:

    BEFORE:string=Many,,,,,, commas,,,,, in,,, this,, sentence.,
    AFTER: string=Many, commas, in, this, sentence.,

    which shows that this approach correctly un-duplicates the source string removing and replacing all repeated characters (commas in our example) with a single one.

    User-defined SAS function for removing any repeated characters

    Let’s use

    libname funclib 'c:\projects\functions';
    proc fcmp outlib=funclib.userfuncs.package1;
       function undupc(str $, clist $) $;
          length x $32767 c $1 cc $2;
          x = str; 
          do i=1 to length(clist);
             c = char(clist,i);
             cc = c||c;
             do while (find(trim(x),cc));
                x = tranwrd(x,cc,c);
          return (x); 

    Code highlights

    Let’s test our newly minted UNDUPC function on the following data:

    data SOURCE;
       infile datalines truncover;
       input str $50.;
    """Repeated "double quotes""""
    [[[""Mixed""]]   characters,,,

    Since our user-defined function is permanently stored in the location specified in the

    options cmplib=funclib.userfuncs;
    data TARGET;
       set SOURCE;
       length new_str $50;
       new_str = undupc(str, ' ,"][');

    This code will remove and replace all repeated sequences of characters ' ',',', '"', ']', and '['. The order of these characters listed in the second argument doesn’t matter. Here is what we get:

    Duplicate characters removal results
    As you can see, we get what we wanted including the functionality of the COMPBL function.

    User-defined CALL routine for removing any repeated characters

    As much as I love user-defined functions, I have an issue with the above undupc user-defined function implementation. It has to do with how the PROC FCMP handles interim character variables length attribute assignment. It does not implicitly inherit their length attribute from another variable as SAS data step does. For example, if you run the following data step:

    data a;
       length x $99;
       y = x;

    variable y will have the length attribute $99 implicitly inherited from the x variable.

    In PROC CFMP function, you can either assign the length attribute to a character variable explicitly with LENGTH or ATTRIB statement (as we did by using length x $32767 ), or it will be set to $33 if you use any other way of implicit assignment. (I leave it up to you guessing why 33 and why not any other number.) Since we wanted to accommodate SAS character strings of any length, we had to explicitly assign our interim variable x length attribute the maximum valid value of $32767. This will inevitably take tall on the function performance as we will have to process longer strings.

    However, we can avoid this issue by using CALL routine instead:

    libname funclib 'c:\projects\functions';
    proc fcmp outlib=funclib.usercalls.package1;
       subroutine undupc(str $, clist $, x $);
          outargs x;
          length c $1 cc $2;
          x = str;
          do i=1 to length(clist);
             c = char(clist,i);
             cc = c||c;
             do while (find(trim(x),cc));
                x = tranwrd(x,cc,c);

    This code is very similar to the user-defined function above with a slight difference. Here, x variable is listed as an argument in the subroutine definition and refers to a SAS data step variable whose length attribute is assigned in the calling data step. Unlike SAS function, SAS subroutine does not return a value; instead, it uses

    options cmplib=funclib.usercalls;
    data TARGET;
       set SOURCE;
       length new_str $50;
       call undupc(str, ' ,"][', new_str);

    And we will get the same results as with the UNDUPC function above.

    Store user-defined functions and subroutines separately

    You can create and have both, user-defined function and call routine with the same name. However, to avoid confusion (and errors) do not store their definitions in the same data table (outlib= option of the PROC FCMP). If they are stored in the same data table, then when used in a DATA step, SAS will pull the latest definition by its name only and that may not be the entity you want.

    Performance benchmarking

    To compare performances of the UNDUPC function vs UNDUPC subroutine we created a rather large data table (1 Million observations) with randomly generated strings (1000 characters long):

    data SASDL.TESTDATA (keep=str);
       length str $1000;
       do i=1 to 1000000;
          str = '';
          do j=1 to 1000;
             str = cats(str,byte(int(rank(' ')+38*rand('UNIFORM'))));

    Then we ran the following 2 data steps, one using the undupc() function, and the other using undupc() call routine:

    options cmplib=funclib.userfuncs;
       set SASDL.TESTDATA;
       length new_str $1000;
       new_str = undupc(str, '#+');
    options cmplib=subrlib.usercalls;
       set SASDL.TESTDATA;
       length new_str $1000;
       call undupc(str, '#+', new_str);

    A quick SAS log inspection reveals that CALL UNDUPC works as much as 3 times faster than UNDUPC function (10 seconds vs. 30 seconds). The time savings may vary depending on your data composition and computing environment, but in any case, if you process high volumes of data you may consider using CALL routine over function. This is not a blanket statement, as it only pertains to this particular algorithm of eliminating character repetitions where we had to explicitly assign the highest possible length attribute value to the interim variable in the function, but not in the CALL routine.

    When we reduced declared length of x from $32767 to $1000 within the user-defined function definition its performance became on par with the CALL routine.

    Additional Resources for SAS character strings processing

    Your thoughts?

    Have you found this blog post useful? Would you vote for implementing UNDUPC as a native built-in SAS function? Please share your thoughts and feedback in the comments section below.

    Removing repeated characters in SAS strings was published on SAS Users.

    10月 152020

    SAS Microsoft partnershipYou might have heard about SAS - Microsoft partnership announced in June 2020 that officially joined the powers of SAS analytics with Microsoft’s cloud technology to further advance Artificial Intelligence (AI).

    This partnership did not just happen out of nowhere. SAS has a long and deep history of integrating with Microsoft technologies. Examples include:

    In this post we will look at a lesser known, but quite useful feature in SAS that allows SAS users to bring many Microsoft Excel functions right to their SAS programs. I hope that many SAS users (not just MS Excel aficionados) will love to discover this functionality within SAS.

    Excel functions as SAS user-defined functions

    SAS has a wide variety of built-in functions, however there are still many Microsoft Excel functions that are not intrinsically implemented in SAS. Luckily, many of them are made available in SAS via

    proc fcmp inlib=SASHELP.SLKWXL listall;

    You can also capture the list of available Excel functions in a SAS data table using ODS OUTPUT with CODELIST= option:

    ods noresults;
    ods output codelist=WORK.EXCEL_FUNCTIONS_LIST (keep=COL1 COL2);
    proc fcmp inlib=SASHELP.SLKWXL listall;
    ods output close;
    ods results;

    From this data table you can produce a nice looking HTML report listing all these functions:

    data WORK.EXCEL_SAS_FUNCTIONS (keep=exc sas arg);
       label exc='Excel Function' sas='SAS Function' arg='Arguments';
       set WORK.EXCEL_FUNCTIONS_LIST (rename=(col2=arg));
       sas = tranwrd(col1,'Function ','');
       exc = tranwrd(sas,'_slk','');
    ods html path='c:\temp' file='excel_sas_functions.html';
    title 'List of Excel functions available in SAS (via SASHELP.SLKWXL)';
    proc print data=EXCEL_SAS_FUNCTIONS label;
    ods html close;

    When you run this code, you should get the following list of Excel functions along with their SAS equivalents:

    List of Excel functions available in SAS (via SASHELP.SLKWXL)
    Obs Excel Function SAS Function Arguments
    1 even even_slk ( x )
    2 odd odd_slk ( x )
    3 factdouble factdouble_slk ( x )
    4 product product_slk ( nums )
    5 multinomial multinomial_slk ( nums )
    6 floor floor_slk ( n, sg )
    7 datdif4 datdif4_slk ( start, end )
    8 amorlinc amorlinc_slk ( cost, datep, fperiod, salvage, period, rate, basis )
    9 amordegrc amordegrc_slk ( cost, datep, fperiod, salvage, period, rate, basis )
    10 disc disc_slk ( settlement, maturity, pr, redemp, basis )
    11 tbilleq tbilleq_slk ( settlement, maturity, discount )
    12 tbillprice tbillprice_slk ( settlement, maturity, discount )
    13 tbillyield tbillyield_slk ( settlement, maturity, par )
    14 dollarde dollarde_slk ( fdollar, frac )
    15 dollarfr dollarfr_slk ( ddollar, frac )
    16 effect effect_slk ( nominal_rate, npery )
    17 coupnum coupnum_slk ( settlement, maturity, freq, basis )
    18 coupncd coupncd_slk ( settlement, maturity, freq, basis )
    19 coupdaysnc coupdaysnc_slk ( settlement, maturity, freq, basis )
    20 couppcd couppcd_slk ( settlement, maturity, freq, basis )
    21 coupdays coupdays_slk ( settlement, maturity, freq, basis )
    22 db db_slk ( cost, salvage, life, period, month )
    23 yield yield_slk ( settlement, maturity, rate, pr, redemp, freq, basis )
    24 yielddisc yielddisc_slk ( settlement, maturity, pr, redemp, basis )
    25 coupdaybs coupdaybs_slk ( settlement, maturity, freq, basis )
    26 oddfprice oddfprice_slk ( settlement, maturity, issue, fcoupon, rate, yield, redemp, freq, basis )
    27 oddfyield oddfyield_slk ( settlement, maturity, issue, fcoupon, rate, pr, redemp, freq, basis )
    28 oddlyield oddlyield_slk ( settlement, maturity, linterest, rate, pr, redemp, freq, basis )
    29 oddlprice oddlprice_slk ( settlement, maturity, linterest, rate, yield, redemp, freq, basis )
    30 price price_slk ( settlement, maturity, rate, yield, redemp, freq, basis )
    31 pricedisc pricedisc_slk ( settlement, maturity, discount, redemp, basis )
    32 pricemat pricemat_slk ( settlement, maturity, issue, rate, yld, basis )
    33 yieldmat yieldmat_slk ( settlement, maturity, issue, rate, pr, basis )
    34 received received_slk ( settlement, maturity, investment, discount, basis )
    35 accrint accrint_slk ( issue, finterest, settlement, rate, par, freq, basis )
    36 accrintm accrintm_slk ( issue, maturity, rate, par, basis )
    37 duration duration_slk ( settlement, maturity, coupon, yld, freq, basis )
    38 mduration mduration_slk ( settlement, maturity, coupon, yld, freq, basis )
    39 avedev avedev_slk ( data )
    40 devsq devsq_slk ( data )
    41 varp varp_slk ( data )

    NOTE: Excel functions that are made available in SAS are named from their Excel parent functions, suffixing them with _SLK to distinguish them from their Excel incarnations, as well as from native SAS functions.

    Examples of Microsoft Excel functions usage in SAS

    In order to use any of these Excel functions in your SAS code, all you need to do is to specify the functions definition data table in the CMPLIB= option:

    options cmplib=SASHELP.SLKWXL;

    Let’s consider several examples.

    ODD function

    This function returns number rounded up to the nearest odd integer:

    options cmplib=SASHELP.SLKWXL;
    data _null_;
       x = 6.4;
       y = odd_slk(x);
       put 'odd( ' x ') = ' y;

    SAS log:
    odd( 6.4 ) = 7

    EVEN function

    This function returns number rounded up to the nearest even integer:

    options cmplib=SASHELP.SLKWXL;
    data _null_;
       x = 6.4;
       y = even_slk(x);
       put 'even( ' x ') = ' y;

    SAS log:
    odd( 6.4 ) = 8

    FACTDOUBLE function

    This function returns the double factorial of a number. If number is not an integer, it is truncated.
    Double factorial (or semifactorial) of a number n, denoted by n!!, is the product of all the integers from 1 up to n that have the same parity as n.
    For even n, the double factorial is n!!=n(n-2)(n-4)…(4)(2), and for odd n, the double factorial is n!! = n(n-2)(n-4)…(3)(1).

    Here is a SAS code example using the factdouble() Excel function:

    options cmplib=SASHELP.SLKWXL;
    data _null_;
       n = 6;
       m = 7;
       nn = factdouble_slk(n);
       mm = factdouble_slk(m);
       put n '!! = ' nn / m '!! = ' mm;

    It will produce the following SAS log:
    6 !! = 48
    7 !! = 105

    Indeed, 6!! = 2 x 4 x 6 = 48 and 7!! = 1 x 3 x 5 x 7 = 105.

    PRODUCT function

    This function multiplies all elements of SAS numeric array given as its argument and returns the product:

    options cmplib=SASHELP.SLKWXL;
    data _null_;
       array x x1-x5 (5, 7, 1, 2, 2);
       p = product_slk(x);
       put 'x = ( ' x1-x5 ')';
       put 'product(x) = ' p;

    SAS log:
    x = ( 5 7 1 2 2 )
    product(x) = 140

    Indeed 5*7*1*2*2 = 140.

    MULTINOMIAL function

    This function returns the ratio of the factorial of a sum of values to the product of factorials:

    MULTINOMIAL(a1, a2, ... , an) = (a1 + a2 + ... + an)! : (a1! a2! ... an!)

    In SAS, the argument to this function is specified as numeric array name:

    options cmplib=SASHELP.SLKWXL;
    data _null_;
       array a a1-a3 (1, 3, 2);
       m = multinomial_slk(a);
       put 'a = ( ' a1-a3 ')';
       put 'multinomial(a) = ' m;

    SAS log:
    a = ( 1 3 2 )
    multinomial(a) = 60

    Indeed (1+3+2)!  :  (1! + 3! + 2!) = 720 : 12 = 60.

    Other Microsoft Excel functions available in SAS

    You can explore other Excel functions available in SAS via SASHELP.SLKWXL user-defined functions by cross-referencing them with the corresponding Microsoft Excel functions documentation (alphabetical or by categories) As you can see in the above List of Excel functions available in SAS, besides mathematical functions exemplified in the previous section, there are also many Excel financial functions related to securities trading that are made available in SAS.

    Additional Resources on SAS user-defined functions

    Your thoughts?

    Have you found this blog post useful? Please share your use cases, thoughts and feedback in the comments below.

    Using Microsoft Excel functions in SAS was published on SAS Users.