topic CALL SCAN to extract substring and create variables in SAS Programming

CALL SCAN to extract substring and create variables

keen_sas — Tue, 13 Aug 2019 13:48:20 GMT

I have a string which consists of sort ,group and case parameters in random order .Task is to separate the values present in sort , group and case into separate variables and store the respective values present in these parameters . Order of their presence is random and it can be missing in few cases as well.All of the parameters are separated by comma(,) if present and sometimes the parameter is missing and sometimes values are missing as well in parameters
Tried this using CALL SCAN , but could not get the proper result. Any suggestions how to extract each sub string or any other approach to extract as shown below.

As below TEXT is the string and variables to be created are SORT, GROUP and CASE and store values in it from the TEXT shown below.

Text	sort	group	case
function(sort= custname birthdt, group= seqno,case=lower);	custname birthdt	seqno	lower
function(group= seqno);		seqno
function(group= Custid,sort =custname );
function(case=upper,group= transid,sort= custname custseq );	custname custseq	transid	upper
function(group= ,sort= custname custseq );	custname custseq

data have ;
length text $200.;
text="function(sort= custname birthdt, group= seqno,case=lower);";output;
text="function(group= seqno);"; output;
text="function(group= Custid,sort =custname ); ";output;
text="function(case=upper,group= transid,sort= custname custseq ); ";output;
text="function(group= ,sort= custname custseq ); ";output;
run ;

Need to create separate variables like group ,case,sort with respective values as shown above using call scan.

Re: CALL SCAN to extract substring and create variables

Shmuel — Tue, 13 Aug 2019 14:21:11 GMT

Let me examine one of your texts:

"function(sort= custname birthdt, group= seqno,case=lower);"

there are few delimiters: "()" assign the start and the end of the text, "=," separate the variable name from its value/

I shall use those delimiters to catch the subtexts and assign into the appropriate variables:

data have ;
length text $200.;
text="function(sort= custname birthdt, group= seqno,case=lower);";output;
text="function(group= seqno);"; output;
text="function(group= Custid,sort =custname ); ";output;
text="function(case=upper,group= transid,sort= custname custseq ); ";output;
text="function(group= ,sort= custname custseq ); ";output;
run ;

data want;
 set have;
     length subtext $50; /* adapt to max length */
	 length sort group case $50;
	 
	 text = scan(text,2,"()"); /* omit "function(" and ')' */
	 do i=1 to 3;
	    subtext = scan(text,i,",");
		varname = scan(subtext,1,'=,');
		var_value = scan(subtext,2,'=,');
		if varname = 'sort' then sort = var_value; else
		if varname = 'group' then group = var_value; else
		if varname = 'case' then case = var_value; else 
		if missing(varname) then leave; /* exit loop */
	end;
	drop subtext;
run;

Re: CALL SCAN to extract substring and create variables

Tom — Tue, 13 Aug 2019 15:35:59 GMT

Parse the string into NAME/VALUE pairs and use PROC TRANSPOSE to make variables based on the NAME values.

data middle;
  set have ;
  obs+1;
  text=scan(text,2,'()');
  do row=1 by 1 until (done);
     next=scan(text,row,',');
     if next=' ' then done=1;
     else do;
       name=scan(next,1,'=');
       value=scan(next,2,'=');
       output;
    end;
  end;
run;

proc transpose data=middle out=want;
  by obs text ;
  id name;
  var value;
run;