topic Parsing character string in SAS Procedures

Parsing character string

lbarwick — Tue, 10 Mar 2015 13:31:37 GMT

Morning,

I have a character field which captures data related to images uploaded to a data system. The system assigns a string to each image, which is essentially a concatenation of a few other fields. The string for each image ends in the following '|FQ|0|F|'. If multiple images are uploaded to a single record, the strings for each image are concatenated into the single character field. There could be up to 10 images concatenated into one string. An example is below containing two images:

$$$.20150309144419.test1.JPG|FND|image 1|FQ|0|F|$$$.20150309144429.test2.JPG|FND|image 2|FQ|0|F|

I would like to parse this concatenated string so that the string for each individual image is stored in a single field, e.g.:

Image1 (column 1)

$$$.20150309144419.test1.JPG|FND|image 1|FQ|0|F|

Image2 (column 2)

$$$.20150309144429.test2.JPG|FND|image 2|FQ|0|F|

I know I can use a count function to determine the number of instances the '|FQ|0|F|' substring appears, but I am a bit at a loss as to where to go from here. Any thoughts?

Re: Parsing character string

Kurt_Bremser — Tue, 10 Mar 2015 13:40:11 GMT

try something like:

(assuming that the whole input line is stored in all_columns)

data want (keep=wanted_string);

set have;

x = index(all_columns,'|FQ|0|F|');

do while (x > 0);

wanted_string = substr(all_columns,1,x-1);

all_columns = substr(all_columns,x+8);

output;

end;

/* process the rest of the line */

wanted_string = all_columns;

if wanted_string > "" then output;

run;

Re: Parsing character string

data_null__ — Tue, 10 Mar 2015 13:52:33 GMT

filename FT15F001 temp lrecl=256; 
data image;
   array image[10] $128; 
   infile FT15F001 dlmstr='|FQ|0|F|' missover; 
   input image;
   parmcards; 
$$$.20150309144419.test1.JPG|FND|image 1|FQ|0|F|$$$.20150309144429.test2.JPG|FND|image 2|FQ|0|F|
;;;;
   run;

Re: Parsing character string

Ksharp — Wed, 11 Mar 2015 16:00:01 GMT

data temp;

text = '$$$.20150309144419.test1.JPG|FND|image 1|FQ|0|F|$$$.20150309144429.test2.JPG|FND|image 2|FQ|0|F|';

id+1;

s=find(text,'|FQ|0|F|','i') ;

x=1;

do while (s > 0);

v=substr(text,x,s+8-x) ;

x=s+8;

s=find(text,'|FQ|0|F|','i',s+8) ;

output;

end;

keep id v;

run;

proc transpose data=temp out=want(drop=_name_) prefix=image_;

by id;

var v;

run;