topic Select max value in proc sql in SAS Procedures

Select max value in proc sql

Lupacante — Tue, 01 Apr 2014 09:54:16 GMT

Hi all,

at the moment, I've got a code which does the following:

1) sorts a DATASET1 by a VAR1 in ascending order, then by VAR2 in ascending order

2) selects removes duplicates of VAR1, but selects the highest value of VAR2

3) appends this to DATASET2 to another one by VAR1

Is it possible to combine these three steps into 1 using a proc sql?

so like

proc sql;

create table NEWTABLE as select

a.*, b.VAR2

from DATASET1 as a

left join DATASET2( [conditions which make 1-3 work]) as b on b.VAR1 = a.VAR1;

quit;

thanks,

Marco

Re: Select max value in proc sql

RW9 — Tue, 01 Apr 2014 10:00:02 GMT

Hi,

Could you clarify your request a bit. A left join is not an append but a merge. You have also put conditions 1-3 in the from of dataset 2 where 1 and 2 are applicable to only dataset 1 from your text. As a rough guess:

proc sql;

create table WANT as

select A.*,

B.VAR3

from (select VAR1,MAX(VAR2) from DATASET1 group by VAR1) A

left join DATASET2 B

on A.VAR1=B.VAR1;

quit;

Re: Select max value in proc sql

Lupacante — Tue, 01 Apr 2014 10:15:33 GMT

Thank you for your answer.

You're right, I got the datasets mixed up.

This is what I would have wanted:

proc sql;

create table NEWTABLE as select

a.*, b.VAR2

from DATASET2 as a

left join DATASET1( [conditions which make 1-3 work]) as b on b.VAR1 = a.VAR1;

quit;

thanks,

Marco

Re: Select max value in proc sql

RW9 — Tue, 01 Apr 2014 10:20:33 GMT

So condition 1 and 2 are fulfilled by the subquery (select VAR1,MAX(VAR2) from DATASET1 group by VAR1). This should return one VAR1 with the maximum VAR2 within that group. Then the other dataset is left joined onto that.