Slect max

[SASPhile]

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

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

[RW9]

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;

[Reeza]

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;

[PGStats]

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

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;

PG