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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.