topic Re: How to find partial strings and count the total amount, then create a macro number? in SAS Procedures

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

ybz12003 — Tue, 20 Jun 2017 21:52:57 GMT

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;

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

art297 — Tue, 20 Jun 2017 22:05:11 GMT

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

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

ybz12003 — Tue, 20 Jun 2017 23:26:03 GMT

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.

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

art297 — Tue, 20 Jun 2017 23:46:50 GMT

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

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

Reeza — Tue, 20 Jun 2017 23:52:29 GMT

Long, but worth reading.

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

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

RW9 — Wed, 21 Jun 2017 08:06:31 GMT

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.

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

ybz12003 — Wed, 21 Jun 2017 13:13:44 GMT

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;