BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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
3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 674 views
  • 0 likes
  • 4 in conversation