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.
@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?
You’re using pass through so you need Oracle SQL not SAS SQL, correct?
Sounds like you want to use the HAVING clause in PROC SQL
HAVING in PROC SQL can select duplicates if they are present in the data.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.