DATA Step, Macro, Functions and more

Replicate first processing using proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

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
Super User
Posts: 7,430

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;

View solution in original post


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

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.

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

Discussion stats
  • 1 reply
  • 166 views
  • 0 likes
  • 2 in conversation