Solved
Contributor
Posts: 33

# 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

Accepted Solutions
Solution
‎02-02-2018 08:05 AM
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;```

All Replies
Solution
‎02-02-2018 08:05 AM
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.