Hi All,
I am trying to swap the word in a string based on the keyword. As per the below table i am trying to swap the word descending prior to its word as below. Original is the variable provided and i am trying to convert into variable REQUIRED only by swapping the word descending just before it. Reason for doing this is Original is the column used in SQL syntax for sorting and Required is the column used in Proc sort Sorting . In SQL Descending option is used after the variable and in proc sort before the variables.Any possibilities to convert as shown below
Original | Required | Comment |
CUSTOMER DOB TERM descending | CUSTOMER DOB descending TERM | |
CUSTOMER DOB descending TERM | CUSTOMER descending DOB TERM | |
CUSTOMER DOB TERM | CUSTOMER DOB TERM | Nochange |
CUSTOMER descending | descending CUSTOMER |
Hi @keen_sas,
If there are no name literals containing blanks (such as 'abc def'n) among the variable names, the below regular expression could be used:
data want;
set have;
required=original; /* set length */
required=prxchange('s/(\S+)\s+(descending)/\2 \1/i', -1, original);
run;
The "i" modifier makes the search case insensitive so that DESCENDING, Descending etc. would be found as well. With a slight modification the abbreviated keyword desc, which is valid in PROC SQL, but not in PROC SORT, could be included:
's/(\S+)\s+(descending|desc)/descending \1/i'
Edit: Improved the regex in order to allow multiple and different whitespace characters between variable name and "descending" (or "desc", resp.) and name literals not containing a whitespace character.
See this:
data have;
input string $60.;
datalines;
CUSTOMER DOB TERM descending
CUSTOMER DOB descending TERM
CUSTOMER DOB TERM
CUSTOMER descending
;
%let refstring = descending;
data want;
set have;
length newstring $60;
index = findw(string,"&refstring.",' ','e');
do i = 1 to index - 2;
newstring = catx(' ',newstring,scan(string,i));
end;
if index then newstring = catx(' ',newstring,"&refstring.",scan(string,index-1));
do i = index + 1 to countw(string);
newstring = catx(' ',newstring,scan(string,i));
end;
drop i index string;
rename newstring=string;
run;
Hi @keen_sas ,
do you mean text cuting&pasting?
data have;
infile cards missover dlm = "|";
input Original : $ 200. Required : $ 200. Comment : $ 20.;
cards;
CUSTOMER DOB TERM descending|CUSTOMER DOB descending TERM|
CUSTOMER DOB descending TERM|CUSTOMER descending DOB TERM|
CUSTOMER DOB TERM|CUSTOMER DOB TERM|Nochange
CUSTOMER descending|descending CUSTOMER|
;
run;
data want;
word = "descending";
set have;
pos = FINDW(Original, word);
if pos = 0 then Result = Original;
else
do;
length start s1 end $ 200;
L = lengthn(word);
end = substr(Original, pos + L);
start = substr(Original, 1, pos-1);
s1 = strip(scan(start, -1));
x = pos-1-lengthn(s1)-1;
if x > 0 then start = substr(Original, 1, x);
else start = "";
Result = catx(" ",start,word,s1,end);
end;
equal = (Result = Required);
output;
drop start word s1 end pos L x;
run;
proc print;
run;
All the best
Bart
Hi @keen_sas,
If there are no name literals containing blanks (such as 'abc def'n) among the variable names, the below regular expression could be used:
data want;
set have;
required=original; /* set length */
required=prxchange('s/(\S+)\s+(descending)/\2 \1/i', -1, original);
run;
The "i" modifier makes the search case insensitive so that DESCENDING, Descending etc. would be found as well. With a slight modification the abbreviated keyword desc, which is valid in PROC SQL, but not in PROC SORT, could be included:
's/(\S+)\s+(descending|desc)/descending \1/i'
Edit: Improved the regex in order to allow multiple and different whitespace characters between variable name and "descending" (or "desc", resp.) and name literals not containing a whitespace character.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.