topic Re: Separating string(variable name) and sub-string into separate variables with multiple delimiter in SAS Programming

Separating string(variable name) and sub-string into separate variables with multiple delimiters

keen_sas — Fri, 27 Dec 2019 13:31:55 GMT

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

Re: Separating string(variable name) and sub-string into separate variables with multiple delimiter

ballardw — Fri, 27 Dec 2019 15:49:45 GMT

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.

Re: Separating string(variable name) and sub-string into separate variables with multiple delimiter

hashman — Sat, 28 Dec 2019 05:44:00 GMT

@keen_sas:

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.

Re: Separating string(variable name) and sub-string into separate variables with multiple delimiter

Tom — Sat, 28 Dec 2019 06:06:17 GMT

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