BookmarkSubscribeRSS Feed
sasbasls
Calcite | Level 5

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

2 REPLIES 2
Ksharp
Super User
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;
Kurt_Bremser
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1120 views
  • 0 likes
  • 3 in conversation