Parsing character string

[lbarwick]

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?

[Kurt_Bremser]

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;

[data_null__]

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;

[Ksharp · Accepted solution]

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;