How do I subtract certain parts of a string value using text rather than positions?

[Pili1100]

DATA WORK.A;
LENGTH Name $10 Note_from_Agent $150;
INFILE DATALINES DELIMITER =','; 
INPUT ID $ Name $ Note_from_Agent $;
CARDS; 
1,Jessica, Carthago delenda est Purpose: Happy sad Action: xoxojaodj 
2,Seth,Non ducor duco Purpose: Something Something Action: xoxojaodj
3,Mort, Vincit qui se vincit Purpose:Tralalala Action: xoxojaodj
4,Eve,Ad astra per aspera Purpose: Hihih Action: xoxojaodj
5,Lauretta, Carpe vinum Purpose: Hehehe Action: xoxojaodj
;
RUN;

I only want to substract the words from Purpose to word before Action

Eg for Lauretta:  Carpe vinum Purpose: Hehehe Action: xoxojaodj -> Purpose: Hehehe

How do I do it?

[ballardw]

Pretty much going to be stuck using a function like Index, Indexw, Find or Findw to find the positions of the key words and use substr function to extract what falls between.

[Amir · Accepted solution]

Hi @Pili1100,

Thanks for supplying a data step with datalines, that really helps.

There can be multiple ways of achieving what you want. I have made some assumptions in the solution below (e.g., no extra ":" in the text), so it might not be as robust for all of your needs, but it appears to work for the sample data you have provided.

data have;
   length name $10 note_from_agent $150;
   infile datalines delimiter =','; 
   input id $ name $ note_from_agent $;
   cards; 
1,Jessica, Carthago delenda est Purpose: Happy sad Action: xoxojaodj 
2,Seth,Non ducor duco Purpose: Something Something Action: xoxojaodj
3,Mort, Vincit qui se vincit Purpose:Tralalala Action: xoxojaodj
4,Eve,Ad astra per aspera Purpose: Hihih Action: xoxojaodj
5,Lauretta, Carpe vinum Purpose: Hehehe Action: xoxojaodj
;


data want;
   set have;

   length excerpt $ 100;

   /* take text between colons, then prefix "Purpose:" and remove "Action" */
   excerpt = cats('Purpose:',substr(scan(note_from_agent,2,':'),1,length(scan(note_from_agent,2,':')) - 6));
run;

If this does not do what you want then please explain with more data where this solution does not work.

Kind regards,

Amir.

[Pili1100]

Thank you, Amir!

Just what I wanted 😁