I have a string with a combination of both variable names and string separated by multiple delimiters as below. Sometimes the sub-string are separated by space and some times by comma (,). These have to be identified and separate the variables and sub string into each unique variable. Commas and spaces are also part of the sub-string when embedded in quotes as below .
data have ;
Have='SIFI " " SIUNIT " (" ATOX ", " GRADE ")"' ;output
Have='SIFI, " " SIUNIT " (", ATOX ", " GRADE ")"' ;output
run ;
Required Output: Each variable or substring should be separated into individual variable
var1 var2 var3 var4 var5 var6 var7 var8
SIFI " " SIUNIT " (" ATOX ", " GRADE ")"
Thanks in advance for your inputs
Your problem description is a bit incomplete. If you expect a value of " " for variable from that string the a simple space is not a delimiter because that value contains a space. Also you have a ", " desired value which contains two delimiters.
So, please provide a more exhaustive set of rules for parsing this data.
1. Treat non-alphanumerics as delimiters.
2. Treat alphanumerics as delimiters.
3. In case #2, kill the endpoint commas separately.
data have ;
have = 'SIFI, " " SIUNIT " (", ATOX ", " GRADE ")"' ; output ;
have = 'SIFI " " SIUNIT " (" ATOX ", " GRADE ")"' ; output ;
run ;
data want (keep = have var:) ;
set have ;
array vv $ 8 var1-var8 v ;
do _n_ = 1 to 4 ;
vv [2 * _n_ - 1] = scan (have, _n_, , "kad") ;
v = scan (have, _n_, , "ad") ;
if char (v, 1 ) = "," then v = substr (v, 2) ;
if char (v, length (v)) = "," then v = substr (v, 1, length (v) - 1) ;
vv [2 * _n_] = v ;
end ;
run ;
Note: I assumed the longest parsed component is $8. Adjust if need be.
Kind regards
Paul D.
You can use the Q modifier on SCAN() and COUNTW() functions.
data have ;
infile cards truncover ;
row+1;
input have $200.;
cards;
SIFI " " SIUNIT " (" ATOX ", " GRADE ")"
SIFI, " " SIUNIT " (", ATOX ", " GRADE ")"
;
data want;
set have ;
do index=1 to countw(have,' ','q');
length term $100 ;
term=scan(have,index,' ','q');
output;
end;
drop have;
run ;
proc print;
run;
Result:
Obs row index term 1 1 1 SIFI 2 1 2 " " 3 1 3 SIUNIT 4 1 4 " (" 5 1 5 ATOX 6 1 6 ", " 7 1 7 GRADE 8 1 8 ")" 9 2 1 SIFI, 10 2 2 " " 11 2 3 SIUNIT 12 2 4 " (", 13 2 5 ATOX 14 2 6 ", " 15 2 7 GRADE 16 2 8 ")"
Your second string doesn't seem to be following the same rules. Did you want to treat comma as another possible delimiter? If so then include it in the SCAN() and COUNTW() function calls.
term=scan(have,index,' ,','q');
If you want to remove those quotes in the even numbered values you can use the DEQUOTE() function.
term=dequote(scan(have,index,' ,','q'));
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.