DATA Step, Macro, Functions and more

Count

Reply
Super Contributor
Posts: 673

Count

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'.
Super Contributor
Posts: 359

Re: Count

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;
Super Contributor
Posts: 673

Re: Count

Flip,
Its returing all the values without the filter
Super Contributor
Posts: 359

Re: Count

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
Super Contributor
Posts: 673

Re: Count

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
Super Contributor
Posts: 359

Re: Count

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;
Ask a Question
Discussion stats
  • 5 replies
  • 147 views
  • 0 likes
  • 2 in conversation