topic Re: Need to condense a PROC SQL into one step in SAS Programming

Need to condense a PROC SQL into one step

SASguyCO — Tue, 02 Jul 2013 22:44:38 GMT

Hi all-

I have two data sets test1 and test2 as such:

Test1

Var1

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!

Re: Need to condense a PROC SQL into one step

Reeza — Tue, 02 Jul 2013 23:45:59 GMT

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;

;

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;

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;

Re: Need to condense a PROC SQL into one step

PGStats — Wed, 03 Jul 2013 00:33:19 GMT

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;

Re: Need to condense a PROC SQL into one step

Tom — Wed, 03 Jul 2013 01:12:15 GMT

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

select * from test2 group by var1 having pack = max(pack) ;

Re: Need to condense a PROC SQL into one step

PGStats — Wed, 03 Jul 2013 02:16:28 GMT

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

Re: Need to condense a PROC SQL into one step

SASguyCO — Wed, 03 Jul 2013 03:28:49 GMT

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.

Re: Need to condense a PROC SQL into one step

Tom — Wed, 03 Jul 2013 03:39:38 GMT

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.

Re: Need to condense a PROC SQL into one step

UrvishShah — Wed, 03 Jul 2013 06:13:33 GMT

Hi,

How about this one...

data one;

input var1;

cards4;

;;;;

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

Re: Need to condense a PROC SQL into one step

Scott_Mitchell — Mon, 08 Jul 2013 13:05:06 GMT

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,

Re: Need to condense a PROC SQL into one step

SASguyCO — Mon, 08 Jul 2013 15:57:11 GMT

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!

Re: Need to condense a PROC SQL into one step

SASPhile — Mon, 08 Jul 2013 21:12:02 GMT

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;