DATA Step, Macro, Functions and more

Slect max

Reply
Super Contributor
Posts: 647

Slect max

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

CustidMr_flag
.0
.1
150130
150131
220170
220171
280110
280111
580140
580140
710130
710131
Super User
Super User
Posts: 7,401

Re: Slect max

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;

Super User
Posts: 17,836

Re: Slect max

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;

Respected Advisor
Posts: 4,649

Re: Slect max

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

PG
Ask a Question
Discussion stats
  • 3 replies
  • 174 views
  • 0 likes
  • 4 in conversation