topic Re: How to flag observations if all values of a variable within a by group are the same in SAS Data Management

How to flag observations if all values of a variable within a by group are the same

KPCklebspn — Wed, 11 Mar 2020 21:25:21 GMT

Hi there,

I have a dataset that looks like this

ID varA

1 0

2 1

3 0

3 1

I want it to look like this (i.e. flag=1 if all values of varA are the same within ID by group):

ID varA flag

1 0 1

2 1 1

3 0 0

3 1 0

How would I do this? Thanks

Re: How to flag observations if all values of a variable within a by group are the same

PeterClemmensen — Wed, 11 Mar 2020 21:31:28 GMT

Try this

data have;
input ID varA;
datalines;
1 0
1 0
2 1
2 1
3 0
3 1
;

proc sql;
   create table want as
   select *, count(distinct varA)=1 as flag 
   from have
   group by ID;
quit;

Re: How to flag observations if all values of a variable within a by group are the same

PaigeMiller — Wed, 11 Mar 2020 21:33:01 GMT

proc summary data=have nway;
    class id;
    var vara;
    output out=stats std=std;
run;

data want;
    merge have stats;
    by id;
    if std=0 then flag=1;
    drop std;
run;

Re: How to flag observations if all values of a variable within a by group are the same

novinosrin — Wed, 11 Mar 2020 21:48:44 GMT

data have;
input ID varA;
datalines;
1 0
1 0
2 1
2 1
3 0
3 1
;
proc sql;
   create table want as
   select *, std(vara)=0 as flag 
   from have
   group by ID;
quit;

Re: How to flag observations if all values of a variable within a by group are the same

KPCklebspn — Wed, 11 Mar 2020 21:55:31 GMT

Thank you, worked! can you pls explain what this bit of syntax means:

count(distinct varA)=1