Count

[SASPhile]

proc sql;
create table hhs_combine_bsm_final as
select d1.*,
case
when d1.phy_name eq d2.physician_name and d1.phy_name ne '' then 'Y'
end as flag
from HHS_combine_bsm d1 left outer join exclude_v1 d2 on d1.phy_name=d2.physician_name;
quit;

Say for the above code we get the data as below:
terrid phyname flag
903110 SAMY BUNN Y
903110 david Herr
903110 peter pan
903110 samuel colt
9031101 sarah Y
9031101 williams Y
9031101 scott
9031101 tramp


How to delete or not display if there is only one physician per terrid whose flag is 'Y'.
for instance we dont want SAMY BUNN, in terrid 930110 as he is the only one physician in that terrid whose flag is 'Y'.

[Flip]

proc sql;
create table two as select b.* from hhs_combine_bsm_final b
where b.terrid in (select a.terrid from (select terrid, count(flg) as cf from hhs_combine_bsm_final
where flg = 'Y' group by terrid) a
where a.cf > 1);
quit;

[SASPhile]

Flip,
Its returing all the values without the filter

[Flip]

I tested it again and am getting the desired result. I did spell flag differently from you. Run each subquerry alone and check.

data one;
input terrid $ phys $ flag $;
cards;
123 aaa Y
123 bbb n
123 ccc n
456 hhh n
456 jjj n
456 kkk Y
456 lll Y
run;


proc sql;
create table two as select b.* from one b where b.terrid in
(select a.terrid from (select terrid, count(flag) as cf from one
where flag = 'Y' group by terrid) a where a.cf > 1);
quit;

14:23 Monday, August 24, 2009

Obs terrid phys flag

1 456 hhh n
2 456 jjj n
3 456 kkk Y
4 456 lll Y

[SASPhile]

Flip,
We need the terrid, but we do not need the phys.we do not want aaa in 123, bu we want bbb and ccc from 123.

so output looks like:
123 bbb n
123 ccc n
456 hhh n
456 jjj n
456 kkk Y
456 lll Y

[Flip]

proc sql;
create table two as select b.* from one b where b.terrid in
(select a.terrid from (select terrid, count(flag) as cf from one
where flag = 'Y' group by terrid) a where a.cf > 1 OR FLAG ne 'Y' );
quit;