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

[fsu1]

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

[Tom]

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.);

[fsu1]

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;

[fsu1]

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

[Patrick]

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;

[Tom]

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.

[Tom]

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;

[andreas_lds]

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;

[Ksharp]

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;