DATA Step, Macro, Functions and more

remove words from one macro var if they are in another.

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

remove words from one macro var if they are in another.

Hello,

I have two macro variables(list_1 and list_2). A list of words have been assigned to each of the variable.
I need to reset list_2 by removing any words that appear in list_1.

%let list_1  = google microsoft yahoo;
%let list_2  = all cnn microsoft fox mtv google;

I need:

list_2_RESET = all cnn fox mtv;

Please advice.

Thanks for your help.


Accepted Solutions
Solution
‎09-17-2013 06:45 PM
Respected Advisor
Posts: 3,892

Re: remove words from one macro var if they are in another.

Using &list_1 you could create a regular expression and then use this regular expression to remove words in &list_2 as demonstrated with below code:

%let list_1  = google microsoft yahoo;
%let list_2  = all cnn microsoft fox mtv google;

/* create regex string */
%let list_1_regex=\b%sysfunc(prxchange(s/ +/ ?\b|\b/oi,-1,&list_1))\b;

/* use SAS regex function prxchange() to remove words from &list_2 */

%let list_2_DIFF=%sysfunc(prxchange(s/&list_1_regex//oi,-1,&list_2));

%put list_1_regex=&list_1_regex;

%put list_2_DIFF=&list_2_DIFF;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: remove words from one macro var if they are in another.

Is the data case sensitive? For example do you fox, Fox and/or FOX in your data and are they considered to be the same? Do you have any compound words to consider such as All Star needing to be treated as one word?

If they come from a data set it may be better to leave them there and use sql with the except operator on distinct values of the variable(s).

Contributor
Posts: 42

Re: remove words from one macro var if they are in another.

This scenario is just a part of the bigger code that I use. After everything is said and done, there are values assigned to the list_1 and list_2 macro vars like I mentioned in the post. So now I need to remove the values if they are in list_1. The case doesn't matter. Lets assume its all lower case.

Solution
‎09-17-2013 06:45 PM
Respected Advisor
Posts: 3,892

Re: remove words from one macro var if they are in another.

Using &list_1 you could create a regular expression and then use this regular expression to remove words in &list_2 as demonstrated with below code:

%let list_1  = google microsoft yahoo;
%let list_2  = all cnn microsoft fox mtv google;

/* create regex string */
%let list_1_regex=\b%sysfunc(prxchange(s/ +/ ?\b|\b/oi,-1,&list_1))\b;

/* use SAS regex function prxchange() to remove words from &list_2 */

%let list_2_DIFF=%sysfunc(prxchange(s/&list_1_regex//oi,-1,&list_2));

%put list_1_regex=&list_1_regex;

%put list_2_DIFF=&list_2_DIFF;

Contributor
Posts: 42

Re: remove words from one macro var if they are in another.

Wow. I would have never thought of using this technique. Thanks a lot Patrick.

Respected Advisor
Posts: 3,124

Re: remove words from one macro var if they are in another.

So slick! Million times better than the one I had in mind, if I had one.
Thanks, Patrick!

Haikuo

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 641 views
  • 3 likes
  • 4 in conversation