I know a lot of you have been programming in SAS for a long time, which is awesome! However, when you do something for a long time, sometimes you get set in your ways and you miss out on new ways of doing things.
Although the COUNT and CAT functions have been around for a while now, I see a lot of customer code that is counting and concatenating text strings the "old-fashioned" way. In this article, I would like to introduce you to the COUNT, COUNTW, CATS and CATX functions. These functions make certain tasks much simpler, like counting words in a string and concatenating text together.
Counting words or text occurrences
Data a; Contributors='The Big Company INC, The Little Company, ACME Incorporated, Big Data Co, Donut Inc.'; Num=count(contributors,'inc','i'); /* the 'i' modifier means to ignore case*/ Put num=; Run;
When we examine the SAS log, we can see that NUM has a value of 3.
Num=3 NOTE: The data set WORK.A has 1 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds
/* DON'T USE - use COUNTW instead */ data a(drop=done i); x='a#b#c#d#e'; do until(done); i+1; y=scan(x,i,'#'); if y='' then done=1; else output; end; run;
I realize this code isn't terrible, but I try to avoid DO UNTIL/WHILE loops if I can. There is always the possibility of going into an infinite loop.
The COUNTW function eliminates the need for a DO UNTIL/WHILE loop.
Here is an example of logic that I use all the time. In this example, I have a macro variable that contains a list of values that I want to loop through. I can use the COUNTW function to easily loop through each file listed in the resolved value of &FILE_NAMES. The code then uses the file name on the DATA statement and the INFILE statement.
%let file_names=01JAN2018.csv 01FEB2018.csv 01MAR2018.csv 01APR2018.csv; %macro test(files); %do i=1 %to %sysfunc(countw(&file_names,%str( ))); %let file=%scan(&file_names,&i,%str( )); data _%scan(&file,1,.); infile "c:\my files\&file"; input region $ manager $ sales; run; %end; %mend; %test(&file_names)
The log is too large to list here, but you can see one of the generated DATA steps in the MPRINT output of this snapshot of the log.
MPRINT(TEST): data _01JAN2018; MPRINT(TEST): infile "c:\my files\01JAN2018.csv"; MPRINT(TEST): input region $ manager $ sales; MPRINT(TEST): run;
This data step will be generated for each file listed.
Counting strings within another text string should be easy to do. The COUNT functions definitely make this a reality!
Concatenating strings in SAS
Now that we know how to COUNT text in SAS, let me show you how to CAT in SAS with the CATS and CATX functions.
Back in the old days, I had hair(!) and we concatenated text strings using double pipes syntax.
This syntax is not too bad, but what if VARn has trailing blanks? Prior to SAS Version 9 you had to remove the trailing blanks from each value. Also, if the text was right justified, you had to left justify the text. This complicates the syntax:
You can now accomplish the same thing using CATS. The
data a; length var1 var2 var3 $12; var1='abc'; var2='123'; var3='xyz'; x=cats(var1,var2,var3); put x=; run;