Help using Base SAS procedures

Can any one help in equivalent proc sql code.

Reply
Contributor
Posts: 49

Can any one help in equivalent proc sql code.

data admit;

input a $ b;

cards;

aa 1

aa 3

bb 1

bb 2

dd 1

cc  1

;

proc sort data=admit;

by  a b;

run;

data one;

set admit;

by  a b;

if first.a;

run;

Can any one help what will be the equivalent proc sql code?

Thank you in advance.

Frequent Contributor
Posts: 106

Re: Can any one help in equivalent proc sql code.

Posted in reply to devarayalu

proc sql;

select  * from (select distinct * from admit) group by a having b=min(b)

order by a;

quit;

Contributor
Posts: 49

Re: Can any one help in equivalent proc sql code.

Posted in reply to pradeepalankar

Thank you so much Pradeep, and need some more information Suppose if we have only one by variable. data one; set admit; by  a; if first.a; run; Then how to select first. observations from the dataset.

Respected Advisor
Posts: 4,922

Re: Can any one help in equivalent proc sql code.

Posted in reply to devarayalu

From the SQL-92 Standard :


A table is a multiset of rows.

multiset: An unordered collection of objects that are not necessarily distinct. The collection may be empty. [my underlying]

Thus any operration that would rely on observation (row) order (first, last, previous, next, etc) cannot be done directly in SQL.

 

PG

PG
Respected Advisor
Posts: 3,156

Re: Can any one help in equivalent proc sql code.

Posted in reply to devarayalu

Although some undocumented feature aka monotonic() may help you on this, but If only have ONE variable, how can you tell whether it is the first one in the group or otherwise, it does not make any difference as the end result, so you may just as well use DISTINCT clause to get the same outcome.

Haikuo

Ask a Question
Discussion stats
  • 4 replies
  • 230 views
  • 0 likes
  • 4 in conversation