How to add flag if the condition is true in same group.
The below table as an example with in vendor_id group if the community is 'Y' then I need a new add variable flag as "Y"
Pract_id | Vendor_ID | Community |
11 | 101 | |
12 | 101 | Y |
13 | 101 | |
14 | 102 | |
15 | 102 | |
16 | 103 | |
17 | 103 | Y |
18 | 104 | Y |
Want output:
Pract_id | Vendor_ID | Community | CBF |
11 | 101 | Y | |
12 | 101 | Y | Y |
13 | 101 | Y | |
14 | 102 | ||
15 | 102 | ||
16 | 103 | Y | |
17 | 103 | Y | Y |
18 | 104 | Y | Y |
I am trying for this code. However it is not working.
proc sql;
create table want as
select *,'Y' as CBF
from have
group by Vendor_ID
having Community='Y'
order by vend_bsid;
quit;
data have;
infile datalines missover;
input Pract_id Vendor_ID Community $;
datalines;
11 101
12 101 Y
13 101
14 102
15 102
16 103
17 103 Y
18 104 Y
;
proc sql;
create table want as
select
a.*,
b.cbf
from
have as a
left join
(
select
distinct vendor_id as vendor_id_d,
community as cbf
from
have
where
community = 'Y'
) as b
on a.vendor_id = b.vendor_id_d;
quit;
UNTESTED CODE
proc freq data=have;
by vendor_id;
table community/out=_counts_;
run;
data want;
merge have _counts_(rename=(community=community1) where=(community1='Y'));
by vendor_id;
if count>=1 then CBF='Y';
drop community1 count percent;
run;
If you want tested code, please provide data as SAS data step code (example)(instructions). We can't write code to work from screen captures.
data have;
infile datalines missover;
input Pract_id Vendor_ID Community $;
datalines;
11 101
12 101 Y
13 101
14 102
15 102
16 103
17 103 Y
18 104 Y
;
proc sql;
create table want as
select
a.*,
b.cbf
from
have as a
left join
(
select
distinct vendor_id as vendor_id_d,
community as cbf
from
have
where
community = 'Y'
) as b
on a.vendor_id = b.vendor_id_d;
quit;
data have;
infile datalines missover;
input Pract_id Vendor_ID Community $;
datalines;
11 101
12 101 Y
13 101
14 102
15 102
16 103
17 103 Y
18 104 Y
;
proc sql;
create table want as
select *,max(Community) as CBF from have group by Vendor_ID order by 1;
quit;
You can just remerge the data with the records where the existing flag is true.
data have;
length Vendor_ID Pract_id 8 Community $1;
input Pract_id Vendor_ID Community ;
datalines;
11 101 .
12 101 Y
13 101 .
14 102 .
15 102 .
16 103 .
17 103 Y
18 104 Y
;
data want;
merge have
have(keep=vendor_id community rename=(community=CBF) where=(CBF='Y'))
;
by Vendor_id;
run;
proc print;
run;
Vendor_ Obs ID Pract_id Community CBF 1 101 11 Y 2 101 12 Y Y 3 101 13 Y 4 102 14 5 102 15 6 103 16 Y 7 103 17 Y Y 8 104 18 Y Y
I see you already have solution but I would've done it with retain statement.
proc sort data=have;
by vendor_id descending community;
run;
data have2;
set have;
by vendor_id descending community;
retain cbf;
if first.vendor_id then cbf='';
if first.vendor_id and community='Y' then cbf='Y';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.