topic Re: VLOOKUP dataset with file in SAS Data Management

VLOOKUP dataset with file

Vinz867 — Fri, 03 Apr 2020 13:07:48 GMT

I have been trying to solve this for awhile and I really need help with DF.

I have file with 5 columns, I need to validate this file against a whitelist file using an expression.

I cannot do a left or right join because it will create additional rows that are not in the original file.

Inner join will remove rows that I need. I also tried a seekbegin() to the whitelist file but I don't think I'm doing it right. Any help will be appreciated!

Pre-expression:

string VALIDITY

file f
string curr_line
string COLUMNA
string COLUMNB
integer COLUMNC

f.open("\\whitelist.csv","r")

Here is the expression:

begin
f.seekbegin(0)
curr_line= f.readline()
while (curr_line)
parse(COLUMNA,",",COLUMNB,",",COLUMNC)
begin
curr_line= f.readline()
while isnull(VALIDITY)
begin
<conditional logic>
then VALIDITY = 'VALID'
else VALIDITY = 'INVALID'
end
end
end

Re: VLOOKUP dataset with file

fsuzhang — Fri, 03 Apr 2020 03:59:39 GMT

try this

proc sql;

select a, b, c, d, e,

case when a=x and b=z then 'TRUE'

else 'FALSE'

end as validity

from file

;

quit;

Re: VLOOKUP dataset with file

Kurt_Bremser — Fri, 03 Apr 2020 06:59:37 GMT

Use a hash object:

data want;
set have;
if _n_ = 1
then do;
  declare hash wl (dataset:"whitelist (keep=x z)");
  wl.definekey("x","z");
  wl.definedone();
end;
validity = ifc(wl.find(key:a,key:b) = 0,"Yes","No");
run;

Untested; for tested code, provide example data in datasteps with datalines.