topic Re: Swapping the WORD in String in SAS Programming

Swapping the WORD in String

keen_sas — Mon, 30 Sep 2019 13:40:53 GMT

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

Re: Swapping the WORD in String

Kurt_Bremser — Mon, 30 Sep 2019 14:16:50 GMT

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;

Re: Swapping the WORD in String

yabwon — Mon, 30 Sep 2019 14:27:49 GMT

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

Re: Swapping the WORD in String

FreelanceReinh — Tue, 01 Oct 2019 06:27:58 GMT

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.