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.
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;
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;
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: