topic Re: Multiple Queries in SAS Programming

Multiple Queries

deega — Tue, 31 Jan 2017 08:49:00 GMT

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;

Re: Multiple Queries

gamotte — Tue, 31 Jan 2017 09:28:11 GMT

Hello,

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

Re: Multiple Queries

RW9 — Tue, 31 Jan 2017 09:40:39 GMT

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.

Re: Multiple Queries

deega — Wed, 01 Feb 2017 02:38:59 GMT

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

Re: Multiple Queries

gamotte — Wed, 01 Feb 2017 09:05:23 GMT

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;

Re: Multiple Queries

RW9 — Wed, 01 Feb 2017 09:16:31 GMT

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

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