DATA Step, Macro, Functions and more

PROC SQL scan function

Reply
Frequent Contributor
Posts: 84

PROC SQL scan function

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

 

 

Super User
Posts: 17,835

Re: PROC SQL scan function

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.

Super User
Posts: 10,500

Re: PROC SQL scan function

Show some actual data.

Frequent Contributor
Posts: 84

Re: PROC SQL scan function

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

Respected Advisor
Posts: 4,649

Re: PROC SQL scan function

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;
PG
Frequent Contributor
Posts: 84

Re: PROC SQL scan function

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

 

Super User
Posts: 17,835

Re: PROC SQL scan function

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

Ask a Question
Discussion stats
  • 6 replies
  • 461 views
  • 0 likes
  • 4 in conversation