Mark obeservations which fulfill specific conditions

[jozuleta]

Hi Experts,

I have a data set with dummy variables and would like to know which of the observations take BOTH values "1" and "0".

Column "Name" and "Dummy X" are already in my data set. Now I would like to add the Column "BOTH" to the data set. If an observation in Column "Name" hast taken both values "1" and "0" , then give me a "YES" in the new column, otherwise a "NO" (this would be the case in this example for "Jorge" and "Tim" since they are the only ones who have taken both values of "1" and "0"). Background: I would like to consider in a different sample only the observations with "YES".

Name	Dummy X	BOTH
Jorge	1	YES
Marc	1	NO
Ben	1	NO
Tim	1	YES
Jorge	0	YES
Marc	1	NO
Ben	1	NO
Tim	0	YES
Jorge	0	YES
Marc	1	NO
Sarah	1	NO
Johanna	0	NO

thanks in advance!

[Shmuel]

If you sort your data by NAME DUMMYX with option NODUPKEY you will get per name either one observation (DUMMYX is either 1 or 0) or two observations (both 1 and 0) :

proc sort data=have out=temp nodupkey;
  by name dummyx;
run;
data want;
 set temp;
   by name;
       if not (first.name and last.name) then both='YES';
       else both='NO';
run;

[novinosrin]

data have;
input Name $	DummyX ;	
cards;
Jorge	1	YES
Marc	1	NO
Ben	1	NO
Tim	1	YES
Jorge	0	YES
Marc	1	NO
Ben 	1	NO
Tim	0	YES
Jorge	0	YES
Marc	1	NO
Sarah	1	NO
Johanna	0	NO
;
data temp;
set have;
n+1;
run;

proc sql;
create table want(drop=n) as
select *,ifc(count (distinct DummyX)>1,'YES','NO') as Both
from temp
group by name
order by n;
quit;

[kiranv_]

something like below in SQL

proc sql;
create table want as 
select name
,dummy_x
,case when count(dummy_x) -sum(dummy_x) = 0 then "N"
else "Y"
end as Both
from have
group by 1;

[ballardw · Accepted solution]

I would recommend to stop thinking in terms of "Yes" and 'NO' but 1 and 0 for a number of reasons. Some analysis procedures require numeric values. And you can always have a custom format display 1 and 0 as needed.

With that in mind:

data example;
 input Name $ Dummy ;
datalines;
Jorge 1  
Marc 1  
Ben 1 
Tim 1 
Jorge 0  
Marc 1 
Ben  1 
Tim 0  
Jorge 0  
Marc 1  
Sarah 1  
Johanna 0 
;
run;

proc sql;
   create table want as
   select name, dummy,  range(dummy) as both 
   from example 
   group by name;
quit;

The RANGE function subtracts the smallest value from the largest. If all of the values are the same the result is 0 (so no which is typical interpretation of 0). Since your values for the dummy variable are 1 and 0 you only get a 1 when both values occur somewhere.

And you could even filter the data at this step:

proc sql;
   create table want as
   select name, dummy,  range(dummy) as both 
   from example 
   group by name
   having both;
quit;

SAS treats 0 as false for comparisons so those that are not "both" are not in the output set.

[novinosrin]

@ballardw Ultraclever !!!!!!!!!,   is blood running in your body ior s it brains running all over?. Why such ideas not striking my mind 

[ballardw]

---

@novinosrin wrote:

@ballardw Ultraclever !!!!!!!!!,   is blood running in your body ior s it brains running all over?. Why such ideas not striking my mind 

---

Mostly 32 years of SAS experience and sometimes a twisted approach to specific sorts of problems.

An assembler class I took many years ago had a class assignment of reversing the digits of a number. The professor expected some sort of MOD function to find the lower order digit, save, and then multiply (or shift register) and repeat until you ran of digits. I used a buffer manipulation that would reverse anything that fit, character or numeric. Still a loop but entirely different.

[jozuleta]

Awesome!