scan string in comma seperated string where column includes a comma

[havmaage]

Hi, 

I need to  scan a string separated by comma where column text sometime contains a comma itself 

here i have extracted som text from a view definition on mssql and in that  def there is an expression 

create view x as

select

Column1 as Column1,

Column2 as Column2,

ISNULL(Column3,' ') as Column3

from y 

When i try to split the string into word separated by comma it doesn't work well 

data teststep; 
i=3;
	res ="Column1 as Column1, Column2 as Column2, ISNULL(Column3,' ') as Column3";
	coldef = scan(res, i, ',');
run;

Gives the result 

ISNULL(Column3

i want it to result in ISNULL(Column3,' ') as Column3

[Astounding]

If you look at RES, it contains 3 commas.  So you are getting everything between the second and third commas.  Here's something that will work:

coldef = catx(',', scan(res, i, ','), scan(res, i+1, ','));

That doesn't make it a good solution, however.  To write a good solution, we would need to know more about the problem ... how do you detect when you actually have a problem with one of the items in your list.  Probably simplest:  get rid of the situation where some commas are text and some are delimiters.  Use a different character as your delimiter.

[FreelanceReinh · Accepted solution]

Hi @havmaage,

To implement the rule "Don't treat commas between parentheses as delimiters," you could temporarily replace the other commas with some odd character (e.g. 'ÿ'='FF'x) which doesn't occur elsewhere, then apply the SCAN function using that character as delimiter and eventually, if necessary, restore the replaced commas. There should be no unbalanced parentheses for this to work.

Example:

data have;
input res $80.;
cards;
Column1 as Column1, Column2 as Column2, ISNULL(Column3,' ') as Column3
f1(a, f2(b, c, d), e) as f, f2(,,0) as g, f3(((3,141),(2,718)),(1,618)) as h
;

data want(drop=_:);
set have;
do _i=1 to length(res);
  _c=char(res,_i);
  _p+(_c='(')-(_c=')');
  if _c=',' & ~_p then substr(res,_i,1)='ÿ';
end;
coldef1=scan(res,1,'ÿ');
coldef2=scan(res,2,'ÿ');
coldef3=scan(res,3,'ÿ');
res=translate(res,',','ÿ');
run;