DATA Step, Macro, Functions and more

Retrieve unique observations

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Retrieve unique observations

 

Hello,

I have two tables :

 

Data t1 ;

Input texte1 ;

Cards ;

ERGO

ERGY

DERMU

FERMI

VERMO

;

Run

 

 

Data t2 ;

Input texte2 ;

Cards ;

ERGO

CRGY

DERMU

ZERMI

VERMO

;

Run

 

I would like to create a 3rd table with items that are only present in one.

 

And get :

 

Texte1   Texte2

ERGY     CRGY

FERMI  ZERMI

 

thank you for your help


Accepted Solutions
Solution
‎02-02-2018 08:05 AM
Super User
Super User
Posts: 9,399

Re: Retrieve unique observations

Run a proc sort nodupkey on both datasets.  As there is not link between the two its hard to say how to put them together:

proc sort data=t1 nodupkey;
  by texte1;
run;
proc sort data=t2 nodupkey;
  by texte2;
run;
data want;
  merge t1 t2;
  by ...;
run;

View solution in original post


All Replies
Solution
‎02-02-2018 08:05 AM
Super User
Super User
Posts: 9,399

Re: Retrieve unique observations

Run a proc sort nodupkey on both datasets.  As there is not link between the two its hard to say how to put them together:

proc sort data=t1 nodupkey;
  by texte1;
run;
proc sort data=t2 nodupkey;
  by texte2;
run;
data want;
  merge t1 t2;
  by ...;
run;
Super User
Posts: 10,681

Re: Retrieve unique observations

Data t1 ;  
Input texte1 $; 
Cards ;  
ERGO  
ERGY  
DERMU  
FERMI  
VERMO  
;  
Run;        
Data t2 ;  
Input texte2 $ ;  
Cards ;  
ERGO  
CRGY  
DERMU  
ZERMI  
VERMO  
;  
Run;
proc sql;
create table x1 as
 select texte1 from t1
 except
 select texte2 from t2 ;

create table x2 as
 select texte2 from t2
 except
 select texte1 from t1 ;
quit;
data want;
 merge x1 x2;
run;
Contributor
Posts: 40

Re: Retrieve unique observations

Hi you can try the below code , but it will give you texte1 and texte2 value with some blank (missing) observations , you have to write one more step to remove the blank and keep only the unique value 

 

can any one suggest how to do that in a single step 

 

 

proc  sql;
create table want as 
select  texte1 ,  texte2 from t1  full join t2
on texte1 = texte2
where texte1 in (select texte1 from t1 left join t2 on texte1=texte2 where texte2 is null)
or texte2 in (select texte2 from t2 left join t1 on texte2=texte1 where texte1 is null);
quit;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 107 views
  • 0 likes
  • 4 in conversation