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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.