DATA Step, Macro, Functions and more

Need to condense a PROC SQL into one step

Reply
Contributor
Posts: 42

Need to condense a PROC SQL into one step

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!

Super User
Posts: 17,912

Re: Need to condense a PROC SQL into one step

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;


Respected Advisor
Posts: 4,659

Re: Need to condense a PROC SQL into one step

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

PG
Super User
Super User
Posts: 6,502

Re: Need to condense a PROC SQL into one step

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

Respected Advisor
Posts: 4,659

Re: Need to condense a PROC SQL into one step

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

PG
Contributor
Posts: 42

Re: Need to condense a PROC SQL into one step

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.


Super User
Super User
Posts: 6,502

Re: Need to condense a PROC SQL into one step

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.

Regular Contributor
Posts: 195

Re: Need to condense a PROC SQL into one step

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 Smiley Surprisedbs

   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

Super Contributor
Posts: 297

Re: Need to condense a PROC SQL into one step

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

Contributor
Posts: 42

Re: Need to condense a PROC SQL into one step

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!

Super Contributor
Posts: 647

Re: Need to condense a PROC SQL into one step

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;

Ask a Question
Discussion stats
  • 10 replies
  • 351 views
  • 7 likes
  • 7 in conversation