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 |
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.