topic Count in SAS Programming

Count

SASPhile — Mon, 24 Aug 2009 17:38:39 GMT

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

Re: Count

Flip — Mon, 24 Aug 2009 18:05:36 GMT

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;

Re: Count

SASPhile — Mon, 24 Aug 2009 18:15:27 GMT

Flip,
Its returing all the values without the filter

Re: Count

Flip — Mon, 24 Aug 2009 18:30:11 GMT

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

Re: Count

SASPhile — Mon, 24 Aug 2009 18:53:33 GMT

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

Re: Count

Flip — Mon, 24 Aug 2009 19:34:06 GMT

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;