topic scan string in comma seperated string where column includes a comma in SAS Programming

scan string in comma seperated string where column includes a comma

havmaage — Mon, 19 Oct 2020 11:13:25 GMT

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

Re: scan string in comma seperated string where column includes a comma

Astounding — Mon, 19 Oct 2020 12:55:18 GMT

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.

Re: scan string in comma seperated string where column includes a comma

FreelanceReinh — Mon, 19 Oct 2020 14:01:23 GMT

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;