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
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.
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.