Multiple Queries

[deega]

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 :

Input	Output
ID	Date	Name	Group	Country	Count
1	1月2日	Jian	ABC	AU	1
1	1月3日	Bian	DEF	RU	1
1	1月4日	Lasie	ABC	UK	1
1	1月5日	Brat	JKL	US	2

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;

[gamotte · Accepted solution]

Hello,

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

[deega]

Thanks...
Any idea how to filter the records that do not have 'ian' and group abc ??

[gamotte]

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;

[RW9]

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 ...

[RW9]

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.