Using 'Not In' in functions

[David_Billa]

I would like to understand how to use 'not in' in SCAN function. What would be wrong in the below code?

5th word from filename is '0417' which is not equals to either '5601' or '6010' or '6020'.So I want value of source=filename in my output but I'm getting source='missing' in my output.

%let function=NL; 

data have;
filename = "IFT_GT_RND_2_0417_1_20201009T075212.csv";
if scan(lowcase(filename),-1,'.')='csv' and "&function" EQ 'NL' and not scan(lowcase(filename),5,'_') in ('5601','6010','6020') then do;
      source=filename;
end;
else source='missing';
run;

[Kurt_Bremser · Accepted solution]

Maxim 2: Read the Log:

 73         %let function=NL;
 74         
 75         data have;
 76         filename = "IFT_GT_RND_2_0417_1_20201009T075212.csv";
 77         if scan(lowcase(filename),-1,'.')='csv' and "&function" EQ 'NL' and not scan(lowcase(filename),5,'_') in
 77       ! ('5601','6010','6020') then do;
 78               source=filename;
 79         end;
 80         else source='missing';
 81         run;
 
 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
       77:69   77:73   

This points to the fact that the NOT operator is resolved first, before the IN. So SAS needs to make a boolean (numeric) value out of the result of the character SCAN function.

There are two ways to make this correct:

if scan(lowcase(filename),-1,'.')='csv' and "&function" EQ 'NL' and not (scan(lowcase(filename),5,'_') in ('5601','6010','6020')) then do;

or

if scan(lowcase(filename),-1,'.')='csv' and "&function" EQ 'NL' and scan(lowcase(filename),5,'_') not in ('5601','6010','6020') then do;