DATA Step, Macro, Functions and more

select all the values of a variable that are not contained in the values of another variable by obs

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

select all the values of a variable that are not contained in the values of another variable by obs

Dear experts,

 

assuming the following data set "have":

data have0;
country="BU"; output;
country="JP"; output;
country="US"; output;
run;
data have1;
value_list="US_ UK _JP NW"; output;
value_list="US_ UK _JP NW"; output;
value_list="US_ UK _JP NW"; output;
run;
data have; merge have0 have1 ;run;

 

I would like to obtain the following one:

 

data want
country="BU"; output;
run;

 

which is the most short, understandable and elegant way? 


Accepted Solutions
Solution
‎07-04-2016 12:26 PM
Trusted Advisor
Posts: 1,228

Re: select all the values of a variable that are not contained in the values of another variable by

Posted in reply to Sir_Highbury

Please try this.

 

data want(keep=country);
set have;
if not find(strip(value_list),strip(country));
run;

View solution in original post


All Replies
Solution
‎07-04-2016 12:26 PM
Trusted Advisor
Posts: 1,228

Re: select all the values of a variable that are not contained in the values of another variable by

Posted in reply to Sir_Highbury

Please try this.

 

data want(keep=country);
set have;
if not find(strip(value_list),strip(country));
run;

Valued Guide
Posts: 505

Re: select all the values of a variable that are not contained in the values of another variable by

Posted in reply to Sir_Highbury
HAVE

data have0;
country="BU"; output;
country="JP"; output;
country="US"; output;
run;

/*
Up to 40 obs WORK.HAVE0 total obs=3

Obs    COUNTRY

 1       BU
 2       JP
 3       US
*/


data have1;
value_list="US_ UK _JP NW"; output;
value_list="US_ UK _JP NW"; output;
value_list="US_ UK _JP NW"; output;
run;

/*
Up to 40 obs WORK.HAVE1 total obs=3

Obs     VALUE_LIST

 1     US_ UK _JP NW
 2     US_ UK _JP NW
 3     US_ UK _JP NW
*/


WANT

Up to 40 obs WORK.WANT total obs=1

Obs    COUNTRY

 1       BU


SOLUTION

* if the list does not change;
data mrg;
     merge have0 have1;
     if index(valuelist,country)=0 then output;
run;quit;


Up to 40 obs WORK.MRG total obs=1

Obs    COUNTRY     VALUELIST

 1       BU       US UK JP NW


☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 202 views
  • 0 likes
  • 3 in conversation