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;
Hello,
In the code you provide, there is a space before ian (and a special character after).
Hello,
In the code you provide, there is a space before ian (and a special character after).
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;
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 ...
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.