topic Re: Proc sort with nodupkey in proc sql in SAS Programming

Proc sort with nodupkey in proc sql

ganandlife — Sat, 25 Nov 2017 13:03:26 GMT

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.

Re: Proc sort with nodupkey in proc sql

PaigeMiller — Sat, 25 Nov 2017 15:29:37 GMT

@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?

Re: Proc sort with nodupkey in proc sql

ganandlife — Sat, 25 Nov 2017 15:45:07 GMT

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

Re: Proc sort with nodupkey in proc sql

Reeza — Sat, 25 Nov 2017 16:07:02 GMT

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

Re: Proc sort with nodupkey in proc sql

ganandlife — Sat, 25 Nov 2017 16:11:12 GMT

yes

Re: Proc sort with nodupkey in proc sql

PaigeMiller — Sat, 25 Nov 2017 16:12:20 GMT

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

Re: Proc sort with nodupkey in proc sql

ganandlife — Sat, 25 Nov 2017 16:17:45 GMT

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

Re: Proc sort with nodupkey in proc sql

PaigeMiller — Sat, 25 Nov 2017 16:37:23 GMT

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

Re: Proc sort with nodupkey in proc sql

Tom — Sat, 25 Nov 2017 16:41:27 GMT

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;