PROC SQL scan function

[SASAna]

Need help with the below question. scan(files,1,' ') ^= 'No' is not working. & all are going to filename1. 

some files have 'No records found' & wanted this to go to filename2.

%Macro test(SUMMAR= );

PROC SQL ;
select files into :filename1 separated by '~'
from &SUMMAR where scan(files,1,' ') ^= 'No' ;
%LET FILE_COUNT1=&sqlobs;
QUIT;

PROC SQL ;
select files into :filename2 separated by '~'
from &SUMMAR where scan(files,1,' ') = 'No' ;
%LET FILE_COUNT2=&sqlobs;
QUIT;

code continues...

%MEND;

Thanks,

Ana

[Reeza]

Comparisons are case sensitive and you could have a trailing space perhaps?

Check what the SCAN function is returing directly with a PUT statement or data step and then compare that to your criteria.

[ballardw]

Show some actual data.

[SASAna]

Hi,

Data is bunch of files and some of them will be like 'No records'.  I want to identify them by splitting them.

Thanks,

Ana

[PGStats]

Try to see the content of that first word with:

PROC SQL ;
select 
	files, 
	scan(files, 1, ' ') as firstWord format=$8.,
	scan(files, 1, ' ') as firstWordHex format=$hex16.
from &SUMMAR;
quit;

[SASAna]

I came up with below code , thought of sharing with you all.

Thanks for all your suggestions. I really appreciate it.

PROC SQL ;
select files into :filename1 separated by '~'
from &SUMMAR where scan(files,1,' ') like '%no%';
%LET FILE_COUNT1=&sqlobs;
QUIT;

PROC SQL ;
select files into :filename2 separated by '~'
from &SUMMAR where scan(files,1,' ') not like '%no%';
%LET FILE_COUNT2=&sqlobs;
QUIT;

Thanks,

Ana

[Reeza]

Your query is using a table. What does that table look like?