Separate blank delimited quoted data fields

[FK1]

Hey Folks,

I have this data set.

As you can see, in the column "rawdata" is a string which consists of double quoted substrings, which are separated by a blank. 

I wanto to extract the data fields, i.e. the double quoted substrings and write them into variables, however SAS does a "wrong" counting:

Lines/Observations 1 to 4 are correct, but lines/observations 5 to 10 are wrong in the sense, that they are all together one substring, because it is quoted in one pair of double quotes!. Also lines 11 and 12 are wrong, as SAS should recognise, that it is a NULL/empty substring.

My code that got me the above screenshot:

data test2;
set work.test1;
do i=1 to countw(rawdata);
a = scan(rawdata,i) ;
output;
end;
run;

Obviously, the the result of coundw(rawdata) ist 12 (substrings) rather than 6, which would be correct.

Any ideas in what way I have to modify the countw - function, in order to get the correct number of substrings?

Cheers,

FK1

[yabwon · Accepted solution]

data test2;
  rawdata = '"a" "b c" "d"';

  do i=1 to countw(rawdata, " ", "Q");
    a = scan(rawdata,i," ","Q") ;
    output;
  end;
run;

I would start with reading doc about options in countw() and scan().

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p18xi2516ihygyn1qg1b1nby326k.h...

Bart

[FK1]

Perfect, yabwon!
I did read through the doc of the "countw" function but obviously not thoroughly enough.

Thank you, for helping out!