Proc sort with nodupkey in proc sql

Reply
Occasional Contributor
Posts: 9

Proc sort with nodupkey in proc sql

 

       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. 

Respected Advisor
Posts: 2,836

Re: Proc sort with nodupkey in proc sql

Posted in reply to ganandlife

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?

--
Paige Miller
Occasional Contributor
Posts: 9

Re: Proc sort with nodupkey in proc sql

Posted in reply to PaigeMiller
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
Super User
Posts: 23,337

Re: Proc sort with nodupkey in proc sql

Posted in reply to ganandlife

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

Occasional Contributor
Posts: 9

Re: Proc sort with nodupkey in proc sql

yes
Respected Advisor
Posts: 2,836

Re: Proc sort with nodupkey in proc sql

Posted in reply to ganandlife

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

--
Paige Miller
Occasional Contributor
Posts: 9

Re: Proc sort with nodupkey in proc sql

Posted in reply to PaigeMiller
I want to have duplicates into new datasets also...
Respected Advisor
Posts: 2,836

Re: Proc sort with nodupkey in proc sql

Posted in reply to ganandlife

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

--
Paige Miller
Super User
Super User
Posts: 7,939

Re: Proc sort with nodupkey in proc sql

[ Edited ]
Posted in reply to ganandlife

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;
Ask a Question
Discussion stats
  • 8 replies
  • 434 views
  • 1 like
  • 4 in conversation