Need to condense a PROC SQL into one step

[SASguyCO]

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!

[Reeza]

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;

[PGStats]

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

[Tom]

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) ;

[PGStats]

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

[SASguyCO]

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.

[Tom]

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.

[UrvishShah]

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

[Scott_Mitchell]

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

[SASguyCO]

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!

[SASPhile]

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;