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?
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;
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 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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.