topic Re: How to extract numeric, character parts that are separated by spaces from a whole string in SAS Programming

How to extract numeric, character parts that are separated by spaces from a whole string

fsu1 — Fri, 07 May 2021 01:40:19 GMT

data u;
input info $ 1 - 50;
cards;
101 pencils 37
102 parker pens 61
103 apple ipod shuffle & nano 08
104 dell studio laptop 03
run;

I tried to write these code:

data check;
set u;
pr_id=input(scan(info,1,' '),3.);
pr_name=put(substr(info,2,' &'), & $30.);
pr_qty=input(scan(info,3,' '),3.);
drop info;
run;

It's not working

Re: How to extract numeric, character parts that are separated by spaces from a whole string

Tom — Fri, 07 May 2021 01:59:15 GMT

You can use negative numbers in SCAN().

  part1 = scan(info,1,' ');
  part3 = scan(info,-1,' ');
  part2 = strip(substrn(info,length(part1)+2,length(info)-length(cats(part1,part3))-2));

It looks like only PART3 needs to be converted to a number. The other two should stay as strings.

  pr_qty=input(part3,32.);

Re: How to extract numeric, character parts that are separated by spaces from a whole string

fsu1 — Fri, 07 May 2021 02:17:51 GMT

actually it has to be read like pr_id, pr_names and pr_qty so i write

data check;
set u;
pr_id=input(scan(info,1,' '),3.);
pr_name=put(substr(info,5,25), $30.);
pr_qty=input(scan(info,-1,' '), 32.);
drop info;
run;

Re: How to extract numeric, character parts that are separated by spaces from a whole string

fsu1 — Fri, 07 May 2021 02:34:46 GMT

but it still giving problem, pr_names taking part of pr_qty in their column.

Re: How to extract numeric, character parts that are separated by spaces from a whole string

Tom — Fri, 07 May 2021 02:44:03 GMT

You have to make the start and length values used in SUBSTR() dynamic. That is why it helps to first create the other two substrings so you can find their lengths.

If you want to generate PR_ID as a number then do the same thing as I showed for PR_QTY.

You can also look into the CALL SCAN() function as that allows you to generate positions. But the logic is more complicated than just make the two sub strings and then using LENGTH() on them.

Re: How to extract numeric, character parts that are separated by spaces from a whole string

Tom — Fri, 07 May 2021 02:46:56 GMT

Here is another way. Split it into ALL of the words and then put the middle ones back to together for your substring.

data check;
  set u;
  pr_id = input(scan(info,1,' '),32.);
  do index=2 to countw(info,' ')-1 ;
    pr_name = catx(' ',pr_name,scan(info,index,' '));
  end;
  pr_qty=input(scan(info,-1,' '),32.);
  drop index info;
run;

Re: How to extract numeric, character parts that are separated by spaces from a whole string

andreas_lds — Fri, 07 May 2021 04:19:32 GMT

I have extend the data with another interesting product ending with a number and i switched from common char functions to a regular expression:

data u;
   input info $ 1 - 50;
   cards;
101 pencils 37
102 parker pens 61
103 apple ipod shuffle & nano 08
104 dell studio laptop 03
555 chanel no. 5 42
;

data want;
   set u;
   
   length 
      rx 8
      pr_id $ 3 /* no need for a numeric var, you won't use it in calculations */
      pr_name $ 30
      pr_qty 8
   ;
   
   retain rx;
   drop rx;
   
   if _n_ = 1 then do;
      rx = prxparse('/(\d+) (.+) (\d+)/');
   end;
   
   if prxmatch(rx, info) then do;
      pr_id = prxposn(rx, 1, info);
      pr_name = prxposn(rx, 2, info);
      pr_qty = input(prxposn(rx, 3, info), ?? 3.);
   end;   
run;

Re: How to extract numeric, character parts that are separated by spaces from a whole string

Patrick — Sat, 08 May 2021 01:51:47 GMT

Posting some code using call scan() like @Tom hinted could be used. Wouldn't have thought about it. Here one way how this could work (IF you've always got exactly three terms in your string).

data have;
  input info $ 1 - 50;
  cards;
101 pencils 37
102 parker pens 61
103 apple ipod shuffle & nano 08
104 dell studio laptop 03
;

data want(drop=_:);
  set have;
  length term1 term2 term3 $20;
  call scan(info,1,_pos1,_len1,' ');
  call scan(info,-1,_pos3,_len3,' ');
  term1=substrn(info,_pos1,_len1);
  term2=substrn(info,_pos1+_len1,_pos3-_len1-1);
  term3=substrn(info,_pos3);
run;

Re: How to extract numeric, character parts that are separated by spaces from a whole string

Ksharp — Fri, 07 May 2021 13:17:10 GMT

data have;
  input info $ 1 - 50;
  cards;
101 pencils 37
102 parker pens 61
103 apple ipod shuffle & nano 08
104 dell studio laptop 03
;

data want;
 set have;
 term1=scan(info,1,' ');
 term2=prxchange('s/^\d+\s+|\d+$//',-1,strip(info));
 term3=scan(info,-1,' ');
run;