Hi all-
I have two data sets test1 and test2 as such:
Test1
Var1
1
2
Test2
Var1 Pack Rank
1 aaa 1a
1 bbb 1b
2 aaa 2a
2 bbb 2b
2 ccc 2c
I want to end up with a data set test3 as such using a single PROC SQL step.
Test3
Var1 Pack Rank
1 bbb 1b
2 ccc 2c
I can do it in two PROC SQL steps using a MAX function and then joining back, but I believe this can be done with "windowing" in just one step but not sure how to exactly?
Any help is greatly appreciated!
I don't know if SAS supports windowing. There are some common alternatives here, or below, but both use two passes of the data anyways.
Can you do a data step.
https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/
data Test1;
input Var1;
cards;
1
2
;
run;
data Test2;
input Var1 Pack $ Rank $;
cards;
1 aaa 1a
1 bbb 1b
2 aaa 2a
2 bbb 2b
2 ccc 2c
run;
proc sql;
create table want as
select * from test2
where var1 in (select var1 from test1)
group by var1
having rank=max(rank)
;
quit;
OR
proc sql;
create table want as
select * from test2 a
right join test1 b
on a.var1=b.var1
group by a.var1
having rank=max(rank)
;
quit;
Hi Reeza, that last query would work better as:
proc sql;
create table want as
select b.var1, pack, rank
from
test2 a right join
test1 b on a.var1=b.var1
group by b.var1
having rank=max(rank);
quit;
PG
I do not understand the question. Reading the link posted by Reeza about "windowing" it seem to just be the ability to include values that are neither group variables or aggregate functions in the variable list. SAS already allows this and it does not require any additional syntax. Now if you want to apply a "window" that is different than that implied by the values of your group by variables then you will need to add additional steps or nested queries.
But your example to me just looks like a simple having clause applied to the sinlge source file TEST2.
select * from test2 group by var1 having rank = max(rank) ;
or
select * from test2 group by var1 having pack = max(pack) ;
Tom, I think the question is inspired by the fact that many versions of SQL do not support auto remerge. - PG
P.S. See SQL Procedure Enhancements SAS(R) 9.3 SQL Procedure User's Guide
Thanks for the replies all. I asked about windowing because I was told by another colleague that this is what I needed to do via SQL syntax. He actually created the syntax for me in SQL but I can't convert it to SAS SQL with the functions he has used. It's all good though. I appreciate the help, but think I'm going to need to go another direction.
If you have a real example of a query that you cannot figure out how to execute then start a new thread.
If it is especially complex to do in SQL it is probably easier to just do with a DATA STEP or their might even be a PROC that can do it for you.
Hi,
How about this one...
data one;
input var1;
cards4;
1
2
3
;;;;
data two;
input var1 pack $ rank $;
cards4;
1 aaa 1a
1 bbb 1b
2 aaa 2a
2 bbb 2b
2 ccc 2c
3 aaa 3a
3 bbb 3b
;;;;
proc sql noprint;
select count(distinct *) into :obs
from one;
reset outobs = &obs.;
create table both as
select a.var1,pack,rank
from one as a
left join
two as b
on a.var1 = b.var1
order by pack desc;
quit;
-Urvish
Hi SASguyCO,
Perhaps I don't understand your question but, why are you so fixated on using SQL to do this?
I would have thought that a couple of sorts and a merge by VAR1 using IF A AND LAST.VAR1 would have the desired effect.
Regards,
SM
Yes a data step is fine too, but it's okay as I've gone about my solution another way. Appreciate everyone's help though!
Does this give you the desired result?
proc sql;
create table test3 as
select a.*
,max(b.pack) as Pack
,max(b.rank) as Rank
from test1 a left outer join test2 b
on a.var1=b.var1
group by a.var1
order by a.var1;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: