Hello Everyone
First, please take a look at this data set:
ID College1 College2 NewVariable
1 A A YES
1 A B YES
1 A A YES
2 A B NO
2 A B NO
2 A B NO
3 A A YES
3 A A YES
3 A A YES
My goal is to create NewVariable. Basically, if none of the values of College2 = College1, then NewVariable = No. If all the values of College2 = College1, or if some of the values of College2 = College1, then NewVariable = Yes.
Thank you!
proc sql;
create table want as select *,sum(college1=college2) as num
from have group by id;
quit;
basically, this creates a new variable NUM which counts the number of matches. I will leave it up to you to turn the 0 match case into "NO" and the cases where there is 1 or more matches into "YES".
proc sql;
create table want as select *,sum(college1=college2) as num
from have group by id;
quit;
basically, this creates a new variable NUM which counts the number of matches. I will leave it up to you to turn the 0 match case into "NO" and the cases where there is 1 or more matches into "YES".
Thank you, Paige. That worked. Also, I appreciate how you answered my main question but left some work for me to do. That is a good teaching technique.
Also, in case anyone wants to know what I did after Paige's section of code:
data want;
set have;
if num = 0 then flag = "N";
else flag = "Y";
run;
Data step solution:
data want;
set have;
by id;
retain newvar;
if first.id then newvar = 'N';
if college1 = college2 then newvar = 'Y';
if last.id;
keep id newvar;
run;
If needed, you can join that back to the original dataset.
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.