Concatenating a variable dynamically in SAS

user_10468613 0
Viewed 170

I want to create a variable that resolves to the character before a specified character (*) in a string. However I am asking myself now if this specified character appears several times in a string (like it is in the example below), how to retrieve one variable that concatenates all the characters that appear before separated by a comma?

Example:

data have;
infile datalines delimiter=",";
input string :$20.;
datalines;
ABC*EDE*,
EFCC*W*d*
;
run;

Code:

data want;
    set have;
    cnt = count(string, "*");
    _startpos = 0;

    do i=0 to cnt until(_startpos=0); 
        before = catx(",",substr(string, find(string, "*", _startpos+1)-1,1));
    end;    
    drop i _startpos;
run;

That code output before=C for the first and second observation. However I want it to be before=C,E for the first one and before=C,W,d for the second observation.

do-loops sas append concatenation
3 Answers

You can use Perl regular expression replacement pattern to transform the original string.

Example:

data have;
infile datalines delimiter=",";
input string :$20.;
datalines;
ABC*EDE*,
EFCC*W*d*
;

data want;
  set have;
  csl = prxchange('s/([^*]*?)([^*])\*/$2,/',-1,string);  /* comma separated letters */
  csl = prxchange('s/, *$//',1,csl);                     /* remove trailing comma */
run;

enter image description here

user_12499620

Make sure to increment _STARTPOS so your loop will finish. You can use CATX() to add the commas. Simplify selecting the character by using CHAR() instead of SUBSTR(). Also make sure to TELL the data step how to define the new variable instead of forcing it to guess. I also include test to handle the situation where * is in the first position.

data have;
  input string $20.;
datalines;
ABC*EDE*
EFCC*W*d*
*XXXX*
asdf
;

data want;
  set have;
  length before $20 ;
  _startpos = 0;
  do cnt=0 to length(string) until(_startpos=0); 
    _startpos = find(string,'*',_startpos+1);
    if _startpos>1 then before = catx(',',before,char(string,_startpos-1));
  end;    
  cnt=cnt-(string=:'*');
  drop i _startpos;
run;

Results:

Obs    string       before    cnt

 1     ABC*EDE*     C,E        2
 2     EFCC*W*d*    C,W,d      3
 3     *XXXX*       X          1
 4     asdf                    0
user_49655490

call scan is also a good choice to get position of each *.

data have;
infile datalines delimiter=",";
input string :$20.;
datalines;
ABC*EDE*,
EFCC*W*d*
****
asdf
;

data want;
  length before $20.;
  set have;

  do i = 1 to count(string,'*');
    call scan(string,i,pos,len,'*');
    before = catx(',',before,substrn(string,pos+len-1,1));
  end;
  put _n_ = +7 before=;
run;

Result:

_N_=1        before=C,E
_N_=2        before=C,W,d
_N_=3        before=
_N_=4        before=
user_98765260
Related