BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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'.
5 REPLIES 5
Flip
Fluorite | Level 6
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
Quartz | Level 8
Flip,
Its returing all the values without the filter
Flip
Fluorite | Level 6
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
Quartz | Level 8
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
Fluorite | Level 6
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 769 views
  • 0 likes
  • 2 in conversation