Using 'Not In' in functions

David_Billa — Thu, 21 Jan 2021 10:45:21 GMT

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;

Re: Using 'Not In' in functions

Kurt_Bremser — Thu, 21 Jan 2021 10:55:22 GMT

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;

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

topic Using 'Not In' in functions in SAS Programming

Using 'Not In' in functions

Re: Using 'Not In' in functions