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
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.);
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;
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;
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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.