topic Re: Separate blank delimited quoted data fields in SAS Programming

Separate blank delimited quoted data fields

FK1 — Wed, 09 Mar 2022 16:58:44 GMT

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

Re: Separate blank delimited quoted data fields

yabwon — Wed, 09 Mar 2022 17:15:32 GMT

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.htm#n1pl9ce4kp7nyvn1jbuk77cav8n8

Bart

Re: Separate blank delimited quoted data fields

FK1 — Wed, 09 Mar 2022 19:05:20 GMT

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

Thank you, for helping out!