Help using Base SAS procedures

Parsing character string

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Parsing character string

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?


Accepted Solutions
Solution
‎03-11-2015 12:00 PM
Super User
Posts: 10,028

Re: Parsing character string

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;

View solution in original post


All Replies
Super User
Posts: 7,782

Re: Parsing character string

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,799

Re: Parsing character string

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;
    Solution
    ‎03-11-2015 12:00 PM
    Super User
    Posts: 10,028

    Re: Parsing character string

    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;

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 3 replies
    • 222 views
    • 0 likes
    • 4 in conversation