CODEWITHSUNDEEP
sasexists
velvetrock
velvetrock

Reputation: 585

SAS how to verifiy if a value exists

In SAS, I want to define a variable new_var by the following criteria:

For each id, if exist reference='A', then new_var='A'; else new_var='B', for exemple:

id  reference  new_var
--  ---------  -------
1   A          A
1   B          A
1   C          A
2   B          B   
2   B          B

Thanks in advance!

Upvotes: 0

Views: 1592

Answers (2)

Longfish
Longfish

Reputation: 7602

If the data is as simple as your example and is sorted by id and reference, then it's just a matter of checking the reference value for each change in id (using first.id). The retain statement copies the new_var value for every subsequent row with the same id.

This method won't work if the reference value being checked is not always first alphabetically.

data have;
input id reference $;
datalines;
1       A
1       B
1       C
2       B
2       B
;
run;

data want;
set have;
by id reference;
retain new_var;
if first.id then do;
    if reference='A' then new_var='A';
    else new_var='B';
end;
run;

Upvotes: 1

Sean
Sean

Reputation: 1120

You can join the observations where reference is A to the original dataset on an id-level basis:

data have;
   input id $ reference $;
   datalines;
     1       A
     1       B
     1       C
     2       B
     2       B  
    ;
run;

proc sql;
    create table want as select
        a.id, a.reference,
        case when b.reference = "A" then "A" else "B" end as new_var
        from have as a
        left join have (where = (reference = "A")) as b
        on a.id = b.id
        order by a.id, a.reference;
quit;

Upvotes: 2

Related Questions