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;
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
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
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.
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
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;
Long, but worth reading.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.