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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.