CODEWITHSUNDEEP
sas
Jennyapple
Jennyapple

Reputation: 117

How to refer a variable from another file in SAS?

Suppose I have two files named "test" and "lookup". The file "test" contains the following information:

COL1  COL2
az    ab
fc    ll
gc    ms
cc    ds

And the file "lookup" has:

VAR
ll
dd 
cc
ab
ds

I want to find those observations, which are in "test" but not in "lookup" and to replace them with missing values. Here is my code: 

data want; set test;
array COL[2] COL1 COL2;
do n=1 to 2;
if COL[n] in lookup.VAR then COL[n]=COL[n];
    else COL[n]=.;
    end;
run;

I tried the above code. But ERROR shows that "Expecting an relational or arithmetic operator". My question is how to refer a variable from another file?

Upvotes: 1

Views: 441

Answers (2)

DomPazz
DomPazz

Reputation: 12465

First, grab the %create_hash() macro from this post.

You need to use a hash object to achieve what you are looking for.

The return code from a hash lookup is zero when found and non-zero when not found.

Character missing values are not . but "".

data want;
set have;
if _n_ = 1 then do;
    %create_hash(lu,var,var,"lookup");
end;

array COL[2] COL1 COL2;
do n=1 to 2;
    var = col[n];
    rc = lu.find();
    if rc then
        col[n] = "";
end;
drop rc var n;
run;

Upvotes: 1

catquas
catquas

Reputation: 720

Here is an alternative approach using proc sql:

proc sql;
create table want as
select case when col1 in (select var from lookup) then '' else col1 end as col1,
    case when col2 in (select var from lookup) then '' else col2 end as col2
from test;
quit;

Upvotes: 0

Related Questions