BookmarkSubscribeRSS Feed
Vinz867
Fluorite | Level 6

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

2 REPLIES 2
fsuzhang
Fluorite | Level 6

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;

Kurt_Bremser
Super User

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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