Proc sort with nodupkey in proc sql

[ganandlife]

       I am trying put all the sas datastep into proc sql steps. below is the question and the scenario.

       I have one proc sql statement connected to oracle(though i have not mentioned oracle connection below)

proc sql;
create table a as select *from new;
quit; 

   Then two proc sort statement based on above dataset a.

proc sort data = a;
by promo descending flag;
run;
proc sort data =a nodupkey out =new1;
by promo;
run;

  Now I want to do these two proc sort statements inside proc sql statement itself. 

[PaigeMiller]

---

@ganandlife wrote:

       I am trying put all the sas datastep into proc sql steps.b

---

Why? The functions in a data step and the functions in SQL do not completely overlap, some things won't be possible in a data step and some things won't be possible in PROC SQL.

---

   Then two proc sort statement based on above dataset a.

proc sort data = a;
by promo descending flag;
run;
proc sort data =a nodupkey out =new1;
by promo;
run;

  Now I want to do these two proc sort statements inside proc sql statement itself. 

---

This isn't clear ... it sounds like you want to run PROC SORT inside SQL which isn't possible.

You can use the SORT clause in PROC SQL, is that what you mean?

[ganandlife]

Nope.....First i need to sort by promo and descending flag, then get the highest flag for each promo and send the remaining to new dataset new 1 –. I want to do all these in single proc sql statement

[Reeza]

You’re using pass through so you need Oracle SQL not SAS SQL, correct?

[ganandlife]

yes

[PaigeMiller]

Sounds like you want to use the HAVING clause in PROC SQL

[ganandlife]

I want to have duplicates into new datasets also...

[PaigeMiller]

HAVING in PROC SQL can select duplicates if they are present in the data.

[Tom]

The proc sort steps will find the first observation that has the maximum value of FLAG within each group defined by PROMO.

In PROC SQL the best you can do is find ALL observations that have the maximum value of FLAG within each group.

proc sql noprint;
create table new1 as
  select *
  from a
  group by promo
  order by promo
  having flag=max(flag)
;
quit;

In ORACLE or other external databases that have newer implementations of SQL syntax you can use ROW_NUMBER to find the first observation. So something like this.

proc sql noprint ;
connect to oracle ..... ;
create table new1 as
select * from connection to oracle
(select *
 from a
 order by promo
 having 1=row_number() over (partition by promo order by promo, flag desc)
)
;
quit;