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 😁
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: