Add flag if the condition is true with in same group.

[Spintu]

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;

[PaigeMiller]

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.

[Spintu]

Thank You. I tested this is working fine.

[maguiremq · Accepted solution]

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;

[Ksharp]

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;

[Spintu]

Thank you

[Tom]

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

[Spintu]

Thank you

[tarheel13]

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;

[Spintu]

Thank you