Solved
Contributor
Posts: 62

# Replicate first processing using proc sql

[ Edited ]

data t;
input name\$ age sal;
cards;
pawan 25 60000
pawan 25 60000
pawan 31 600000
raj 22 50000
rahul 24 450000
rahul 24 422222
pawan 25 60000
;run;

i have this dataset and i want only first unique record through sql.

the desires output is

pawan 25 60000

raj 22 50000

rahul 24 450000

can do with proc sort but want to do with proc sql.

Accepted Solutions
Solution
‎04-19-2016 12:16 AM
Super User
Posts: 9,599

## Re: sql

Define "first record" in terms of the data you have.  You can't because you are assuming first based on logical observation number.  In my opinion this is not a valid technique, observation position can change, and this is why directly in SQL you can't do this.  You need to provide an exact sort based on the data elements.  In conclusion I would ascertain why this record:

rahul 24 450000

Should appear before:

rahul 24 422222

As logically the reverse should be true - or is it reverse sorted?

Now you can fool it by adding an extra data element:

```data t;
input name\$ age sal;
cards;
pawan 25 60000
pawan 25 60000
pawan 31 600000
raj 22 50000
rahul 24 450000
rahul 24 422222
pawan 25 60000
;
run;

data t;
set t;
n=_n_;
run;

proc sql;
create table WANT as
select  *
from    T
group by NAME
having   N=min(N);
quit;```

All Replies
Solution
‎04-19-2016 12:16 AM
Super User
Posts: 9,599

## Re: sql

Define "first record" in terms of the data you have.  You can't because you are assuming first based on logical observation number.  In my opinion this is not a valid technique, observation position can change, and this is why directly in SQL you can't do this.  You need to provide an exact sort based on the data elements.  In conclusion I would ascertain why this record:

rahul 24 450000

Should appear before:

rahul 24 422222

As logically the reverse should be true - or is it reverse sorted?

Now you can fool it by adding an extra data element:

```data t;
input name\$ age sal;
cards;
pawan 25 60000
pawan 25 60000
pawan 31 600000
raj 22 50000
rahul 24 450000
rahul 24 422222
pawan 25 60000
;
run;

data t;
set t;
n=_n_;
run;

proc sql;
create table WANT as
select  *
from    T
group by NAME
having   N=min(N);
quit;```
🔒 This topic is solved and locked.