topic Re: PROC SQL scan function in SAS Programming

PROC SQL scan function

SASAna — Tue, 07 Feb 2017 21:48:54 GMT

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 — Tue, 07 Feb 2017 21:56:37 GMT

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 — Tue, 07 Feb 2017 23:45:07 GMT

Show some actual data.

SASAna — Wed, 08 Feb 2017 03:12:43 GMT

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 — Wed, 08 Feb 2017 03:31:56 GMT

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;

Reeza — Wed, 08 Feb 2017 03:32:34 GMT

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

SASAna — Wed, 08 Feb 2017 22:51:10 GMT

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