topic Re: Retrieve unique observations in SAS Programming

Retrieve unique observations

WilliamB — Fri, 02 Feb 2018 11:05:54 GMT

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

Re: Retrieve unique observations

RW9 — Fri, 02 Feb 2018 11:15:34 GMT

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;

Re: Retrieve unique observations

Ksharp — Fri, 02 Feb 2018 12:46:19 GMT

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;

Re: Retrieve unique observations

soham_sas — Fri, 02 Feb 2018 17:01:10 GMT

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;