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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.