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

Solved
Super Contributor
Posts: 398

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

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;

Accepted Solutions
Solution
‎06-21-2017 09:13 AM
PROC Star
Posts: 8,163

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

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

All Replies
PROC Star
Posts: 8,163

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

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

Super Contributor
Posts: 398

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

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.

Solution
‎06-21-2017 09:13 AM
PROC Star
Posts: 8,163

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

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

Super Contributor
Posts: 398

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

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;

Super User
Posts: 23,683

Super User
Posts: 9,599

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

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.

☑ This topic is solved.