How to remove all occurrences (0 or many) of a character between two lines in SAS

Question

I am trying to parse a .json file in SAS. To process lists in a .json file, I would like to remove all commas from [item1, item2, item3, .... itemn], but keep commas that are not in [].

I think I should do this using the prxchange regular expression ... I can make it work for two lists of elements, but I can’t figure out how to change it to work with lists of different amounts.

newvariable=prxchange('s/(\[\w+),(\w+\])/$1 $2',-1,oldvariable);

Examples:

 oldvariable = "{"hospital": "NOP", "drugs": ["penicillin", "ampicillin", "cephalosporin"]}" newvariable = "{"hospital": "NOP", "drugs": ["penicillin" "ampicillin" "cephalosporin"]}" oldvariable = "{"hospital": "KOP", "drugs": ["tetracycline"]}" newvariable = "{"hospital": "KOP", "drugs": ["tetracycline"]}" 

Perhaps there is a better way to approach this ...

Answer 1

Sometimes the easiest way to handle a regular expression is to break it down into steps. In this case, output the array first, then replace the commas with spaces:

 data _null_; oldvariable = '{"hospital": "NOP", "drugs": ["penicillin", "ampicillin", "cephalosporin"]}'; arrayExpr=prxparse( '/\[[^]]+\]/' ); call prxsubstr( arrayExpr, oldvariable, position, length ); put position length; newvariable=cat( substr( oldvariable, 1, position - 1 ), prxchange( 's/, / /', -1, substr( oldvariable, position, length ) ), substr( oldvariable, position + length ) ); put newvariable; run; 

Your original regex has had some problems. Of the many regex-helper sites, this is my favorite .

Answer 2

You can use the SAS DSD option (which allows you to use quotation marks to ignore the built-in delimiters) if you make a smaller prxchange similar to the Leo clause.

 data have; infile datalines dlm=',' dsd; input @; _prx = prxparse('s~\[([0-9,]*?)\]~"$1"~io'); _prxm = prxmatch(_prx,_infile_); if _prxm then call prxchange(_prx,-1,_infile_); _test_=_infile_; input ab $ cd $; datalines; 1,Hello,2,3 2,Goodbye,3,[4,5,6] ;;;; run; 

In your case, I'm not sure double quotes will work, as they make sense in JSON, but you can also use single quotes.