BookmarkSubscribeRSS Feed
SASguyCO
Calcite | Level 5

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!

10 REPLIES 10
Reeza
Super User

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
Opal | Level 21

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
Tom
Super User Tom
Super User

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
Opal | Level 21

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
SASguyCO
Calcite | Level 5

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1325 views
  • 7 likes
  • 7 in conversation