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?
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.
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.
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.
Thank you, Amir!
Just what I wanted 😁
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.