hi,
I have data as below -
ID_no ID_Val
1234 line1
1234 line1
1234 abcd
2345 line1
2345 line1
2345 abcd
3456 line1
3456 line2
I want to create a variable for flag, when an ID_No encounters value for ID_Val = abcd, but to output all ID_No and flag.
Output will be -
ID_No ID_Flag
1234 abcd
2345 abcd
3456 none
(means when value = abcd isn't there for any of the ID_No, then ID_Flag will be 'none' (n/a)
ID_no should not repeat in the end data.
thanks in advance,
basels
data have;
input ID_no ID_Val $;
cards;
1234 line1
1234 line1
1234 abcd
2345 line1
2345 line1
2345 abcd
3456 line1
3456 line2
;
run;
proc sql;
create table want as
select distinct a.*,coalescec(ID_Val,'none') as ID_Flag
from (select distinct ID_no from have) as a left join
(select * from have where ID_Val='abcd') as b
on a.ID_no =b.ID_no ;
quit;
Here's a DATA step method:
data have;
input ID_no ID_Val $;
cards;
1234 line1
1234 line1
1234 abcd
2345 line1
2345 line1
2345 abcd
3456 line1
3456 line2
;
run;
data want;
set have;
by ID_no;
length ID_Flag $4;
retain ID_Flag;
if first.ID_no then ID_Flag = 'none';
if ID_VAL = 'abcd' then ID_Flag = 'abcd';
if last.ID_no then output;
keep ID_no ID_Flag;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.