DATA Step, Macro, Functions and more

Multiple Queries

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Multiple Queries

Hello Everybody,

 

I want to create a new variable (Count) based on multiple queries in a group:

Observations are grouped by ID. Condition 1 is : Desired variable 'count' increases by 1 if variable 'Country' is US. Condition2 is if variable 'Group' is 'ABC' and variable 'Name' contains 'ian' then also count increases by 1. Something like below :

InputOutput
IDDateNameGroupCountryCount
11月2日JianABCAU1
11月3日BianDEFRU1
11月4日LasieABCUK1
11月5日BratJKLUS2

 

I wrote the following program but dont know why its not giving the desired result.

 

data Out;
set Input ;
by ID Date ;
if first.ID then count=0;
if (find(Name,' ian゙','i') and Group = 'ABC') then count + 1 ;
else if Country="US" then count + 1;
run;


Accepted Solutions
Solution
‎01-31-2017 09:37 PM
Regular Contributor
Posts: 233

Re: Multiple Queries

[ Edited ]

Hello,

In the code you provide, there is a space before ian (and a special character after).

View solution in original post


All Replies
Solution
‎01-31-2017 09:37 PM
Regular Contributor
Posts: 233

Re: Multiple Queries

[ Edited ]

Hello,

In the code you provide, there is a space before ian (and a special character after).

Contributor
Posts: 71

Re: Multiple Queries

Thanks...
Any idea how to filter the records that do not have 'ian' and group abc ??
Regular Contributor
Posts: 233

Re: Multiple Queries

You can explicitely output only the records that satisfy the condition with an "output" statement.

Whenever a data step contains an output statement, the automatic output of input records

is disabled.

 

data want;
set Input;
by ID Date;
if first.ID then count=0;
if (find(Name,'ian','i') and Group = 'ABC') then do;
	count + 1 ;
	output;
end;
else if Country="US" then count + 1;
run;
Super User
Super User
Posts: 7,942

Re: Multiple Queries

Always a good idea to post test data, in the form of a datastep.  To get those with the string reverse the logic gate and include any extras, from:

if index(name,'ian') > 0 then ...

To

if index(name,'ian') = 0 and group ne "abc" then ...

Super User
Super User
Posts: 7,942

Re: Multiple Queries

The text string ' ian゙' is not found in any of the rows of Name.  If you want to find Jian and Bian then:

if index(name,'ian') > 0 then ...

 

Also, good idea to post test data in the form of a datastep.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 175 views
  • 1 like
  • 3 in conversation