topic Re: How to extract sequence from string and save as separate value in another column with counter in SAS Programming

How to extract sequence from string and save as separate value in another column with counter

appleorange — Fri, 13 Jan 2023 19:48:34 GMT

I have data entered freestyle as a comment that looks like this:

pn1111-pn2222-pn3333-pn4444-1icc1111-1icc2222-1icc3333-2icc1111

I am using code like this to separate them out into pn1111, pn2222, etc.

data want; set codes;
y=compbl(translate(codes,' ',"||:,*~’°-!';()®""@#$%^&©+=\/|[]}{]{?><ÉÑËÁ’ÍÓÄö‘—È…...")); run;

data want2; set want;
y2=lowcase(y); run;

data want3; set want2;
y3=compress(y2); run;

data try; set want3;
y2=tranwrd(trim(y2), " ", ",");
call symput('y', y2);
run;
data middle2;
set try;
length term $15;
do i=1 by 1 until (term=' ');
term=strip(scan(y2,i,','));
if term ne ' ' then output;
end;
run;

The issue is that if someone pus a space like pn 1111 in the original comment, this code creates two separate codes in the term variable, pn, and 1111. Is there a way to format the comment somehow allowing nospace between the prefixes 'pn, 1icc, 2icc' and 4 digits, but retaining the natural space between each separate code? Or if there's another method to do this?

Re: How to extract sequence from string and save as separate value in another column with counter

ballardw — Fri, 13 Jan 2023 20:47:55 GMT

There is probably a slick solution with some PRXCHANGE rules but I'm not very good with those.

You could take a separate pass through the value with the TRANWRD function to replace "pn " with "pn" before the code you currently use.

I would make sure that the case is set before this step if you think you may have Pn pN or PN values.

result = tranwrd(codes,"pn ","pn");
<next code>

Caution with the Tranwrd function. Your need here should not be a problem because you are removing characters. If you use it to replace a string with a longer value value you run into the possibility of creating a resulting string that will not fit into the current defined length of the existing variable. So when adding characters you may need to define a length for new variable long enough to hold the changes and assign the result to that new variable.

You may also want to examine the modifiers available for the Compress function.

data example;
   x='1!2@3#4$ abc ?><":pn123-pn456';
   y=compress(x,' -','kad');
run;

the 'kad' uses the k to keep the characters in the list, the a adds all alphabetic characters and d adds digits. So this keeps just letters, digits, spaces and the - character (if interested).

Lots shorter typing than that long list you use in Translate.

Re: How to extract sequence from string and save as separate value in another column with counter

appleorange — Fri, 13 Jan 2023 22:25:54 GMT

Thank you for the suggestion with the separate pass through of removing the spaces first. That seemed to solve the issue.

I have been looking at PRXCHANGE too but haven't found that slick solution yet!

Re: How to extract sequence from string and save as separate value in another column with counter

Patrick — Fri, 13 Jan 2023 23:45:08 GMT

Scan() should do the job nicely.

If there would be a need for RegEx then CALL PRXNEXT() and the sample code provided in the docu would already get you very close to the solution.

data sample;
  have='pn1111-pn2222-pn3333-pn4444-1icc1111-1icc2222-1icc3333-2icc1111';
  length term $10;
  term=scan(have,1,'-');
  _i=1;
  do while(not missing(term));
    output;
    term=scan(have,_i,'-');
    _i+1;
  end;
run;

Re: How to extract sequence from string and save as separate value in another column with counter

andreas_lds — Mon, 16 Jan 2023 08:05:33 GMT

I don't see the need for fancy regex, the duo countw + scan seem capable of solving the problem:

data want;
   have='pn1111-pn2222-pn3333-pn4444-1icc1111-1icc2222-1icc3333-2icc1111';
   length term $10;

   do i = 1 to countw(have, '-');
      term = scan(have, i, '-');
      output;
   end;
run;