BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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
Rhodochrosite | Level 12

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
Opal | Level 21

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
Rhodochrosite | Level 12

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1212 views
  • 1 like
  • 4 in conversation