How to find partial strings and count the total amount, then create a macro number?

[ybz12003]

Hello:

I have the following DATA program.  Is there a way to write the PROC SQL program list below?  Thanks.

data want;

set Have;

if index(Name,'If_True')>0 and index(Name,'kary')>0 then do;

N1=1;

end;

if index(Name,'If_False')>0 and index(Name,'Paul')>0 then do;

N2=1;

end;

run;

proc means data=want sum;

var N1 N2;

run;

proc SQL;

SELECT

sum(case when find (name,'If_True')>0 then 1 else 0 end) into: N1 FROM have;

SELECT

sum(case when find (name,'If_False')>0 then 1 else 0 end) into: N2 FROM have;

quit;

[art297]

What do you think is wrong with your current SQL code? It appears to already create the two macro variables that you want.

Art, CEO, AnalystFinder.com

[ybz12003]

There is nothing wrong with the SQL codes.  In fact, they are great.  I am trying to reproduce those codes based on my Data step requirement.   The SQL seems only good at finding one single string 'If_True',  but I am looking for the match both 'If_True' and 'Kary'.  I have 200 similar rows showing in data steps, that is why I'm looking for the simpler codes to accomplish the task.

[art297 · Accepted solution]

You can include both conditions in your sql clause:

data have;
informat name $80.;
  input name &;
  cards;
John If_True kary
John If_True Mary
John If_True kary
John If_True kary
Tom If_Not Carol
Tom If_Not Carol
Tom If_Not Carol
Joe If_True Jane
Joe If_False Jane
Joe If_False Paul
Joe If_False Jane
Paul If_False Jane
Joe If_False Jane
Joe If_False Jane
;

proc SQL;
SELECT
sum(case when find (name,'If_True')>0 and index(Name,'kary')>0 then 1 else 0 end) into: N1 FROM have;
SELECT
sum(case when find (name,'If_False')>0  and index(Name,'Paul')>0 then 1 else 0 end) into: N2 FROM have;
quit;

%put &n1;
%put &n2;

Art, CEO, AnalystFinder.com

[ybz12003]

Thanks for your help, Art297  That is exactly what I want.    Just one more question,  is there a way to create a table to list all of the macro N? I found the table I created only show the last macro N.

proc SQL;
SELECT
sum(case when find (name,'If_True')>0 and index(Name,'kary')>0 then 1 else 0 end) into: N1 FROM have;
SELECT
sum(case when find (name,'If_False')>0  and index(Name,'Paul')>0 then 1 else 0 end) into: N2 FROM have;
quit;

%put &n1;
%put &n2;

[Reeza]

Long, but worth reading. 

http://www.catb.org/~esr/faqs/smart-questions.html

[RW9]

What is it your actually trying to do?  Follow the guidance next to the Post button, post test data in the form of a datastep, example output, logic etc.  None of that code really makes any sense to me.  The first block seems to create some conditonal variables, then means those variables - this could be done in one simple SQL step.  The second block seems to be trying to put a condiiton into a macro variable, which will not work.