topic Re: Slect max in SAS Programming

Slect max

SASPhile — Fri, 14 Mar 2014 15:06:52 GMT

how to select custid whose max mr_flag is 1

which means custid 58014 is not selected as it has 0 as mr_flag. rest all have atleast one value for mr_flag with a value 1

Custid Mr_flag

15013 0

15013 1

22017 0

22017 1

28011 0

28011 1

58014 0

71013 0

71013 1

Custid	Mr_flag
.	0
.	1
15013	0
15013	1
22017	0
22017	1
28011	0
28011	1
58014	0
58014	0
71013	0
71013	1

Re: Slect max

RW9 — Fri, 14 Mar 2014 15:11:56 GMT

Hi,

Like this? Gets a bit more complicated if you have more than one variable as you will then need to left join the data back on where custid=sustid and max(flag)=flag.

data have;
attrib custid format=$10. mr_flag format=best.;
custid=15013; mr_flag=0; output;
custid=15013; mr_flag=1; output;
run;

proc sql;
create table WANT as
select distinct
          CUSTID,
          MAX(MR_FLAG) as FLAG
from   HAVE
group by CUSTID;
quit;

Re: Slect max

Reeza — Fri, 14 Mar 2014 15:37:06 GMT

Using a subquery is a logical way to go, I find it's easy to understand:

The subquery finds all ids where the mr_flag is ever set to 1 and then main query pulls all records from teh table for that custID.

proc sql;

create table want as

select *

from have

where custin in (

select distinct custid

from have where mr_flag=1);

quit;

Re: Slect max

PGStats — Sat, 15 Mar 2014 02:46:13 GMT

I would do it this way:

data have;

input CustId Mr_flag;

datalines;

15013 0

15013 1

22017 0

22017 1

28011 0

28011 1

58014 0

71013 0

71013 1

;

proc sql;

create table want as

select *

from have

group by CustId

having max(Mr_flag)=1

order by CustId, Mr_flag;

select * from want;

quit;