topic Re: Replace ALL duplicate observations with blanks in SAS Data Management

Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 13:25:46 GMT

Hi all,

I have a dataset that looks like this

ID V1 V2 V3

A 12 13 14

A 12 14 14

A 12 15 14

I want it to look like:

ID V1 V2 V3

A . 13 .

A . 14 .

A . 15 .

Basically, replace those with ID = A, and variables with duplicate values as missing.

if lag(ID)=ID and lag(V1)=V1 then call missing(V1);
if lag(ID)=ID and lag(V2)=V2 then call missing(V2);
if lag(ID)=ID and lag(V3)=V3 then call missing(V3);

I used this code but it only replaces subsequent duplicates, not all!

Thanks a lot for your help!

Re: Replace ALL duplicate observations with blanks

RW9 — Fri, 07 Sep 2018 13:37:37 GMT

Well, it doesn't make much sense, but I would suggest flipping the data over:

data have;
  input id $ v1 v2 v3;
datalines;
A 12 13 14
A 12 14 14
A 12 15 14
;
run;
proc transpose data=have out=inter;
  by id;
  var v:;
run;
data inter;
  set inter;
  array i col:;
  if sum(of i{*})/dim(i)=i{1} then call missing(of col:);
run;
proc transpose data=inter out=want;
  by id;
  var col:;
run;

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 13:54:19 GMT

@ChrisNZ , i took the solution you posted from a different question, but wondering if there is any way to delete the original of the duplicates, and not just the subsequent ones!?

Re: Replace ALL duplicate observations with blanks

RW9 — Fri, 07 Sep 2018 13:55:24 GMT

Nothing about this post makes sense?

Re: Replace ALL duplicate observations with blanks

novinosrin — Fri, 07 Sep 2018 14:22:09 GMT

data have;
input ID $ V1 V2 V3;
cards;
A  12 13 14
A 12 14 14
A 12 15 14
;


proc means data=have noprint;
by id;
var v:;
output out=w(drop=_:) std=/autoname;
run;
data want;
merge have w;
by id;
array s1 v1-v3;
array s2 v1_stddev--v3_stddev;
do _n_=1 to dim(s1);
if s2(_n_)=0 then call missing(s1(_n_));
end;
keep id v1-v3;
run;

Re: Replace ALL duplicate observations with blanks

Astounding — Fri, 07 Sep 2018 14:34:08 GMT

What about this data? What would you like the result to e for V1?

ID V1 V2 V3

A 12 13 14

A 15 14 14

A 12 15 14

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 14:54:23 GMT

Anywhere that it's a duplicate should be deemed missing.

ID V1 V2 V3
A . 13 .
A 15 14 .
A . 15 .
Thanks!

Re: Replace ALL duplicate observations with blanks

Reeza — Fri, 07 Sep 2018 14:56:50 GMT

Why not just remove them from the data set?

Re: Replace ALL duplicate observations with blanks

Ksharp — Fri, 07 Sep 2018 15:09:30 GMT

Sorry. It is to OP. EDITED.

data have;
  input id $ v1 v2 v3;
datalines;
A  12 13 14
A 15 14 14
A 12 15 14
;
run;
proc sort data=have;
by id v1;
run;
data have;
 set have;
 by id v1;
 if not (first.v1 and last.v1) then call missing(v1);
run;

proc sort data=have;
by id v2;
run;
data have;
 set have;
 by id v2;
 if not (first.v2 and last.v2) then call missing(v2);
run;

proc sort data=have;
by id v3;
run;
data have;
 set have;
 by id v3;
 if not (first.v3 and last.v3) then call missing(v3);
run;

proc print;run;

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 15:03:38 GMT

Because I want to keep the datapoints (either by obs or var) that are not duplicates. For instance, all the V2 datapoints for ID=A in this example.

Re: Replace ALL duplicate observations with blanks

Astounding — Fri, 07 Sep 2018 15:06:50 GMT

Let me give you a solution that is straightforward and reliable, but tedious and time-consuming. We will see if anyone comes up with a better solution. (Hope somebody does!)

Process each variable separately:

proc sort data=have;

by id v1;

run;

data temp;

set have;

by id v1;

if first.v1=0 or last.v1=0 then v1=.;

run;

proc sort data=temp;

by id v2;

run;

data temp;

set temp;

by id v2;

if first.v2=0 or last.v2=0 then v2=.;

run;

proc sort data=temp;

by id v3;

run

data want;

set temp;

by id v3;

if first.v3=0 or last.v3=0 then v3=.;

run;

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 15:09:58 GMT

This only returns one observation per ID when I print the temp file. Am I missing something?

Re: Replace ALL duplicate observations with blanks

Ksharp — Fri, 07 Sep 2018 15:11:40 GMT

Code is updated .

Re: Replace ALL duplicate observations with blanks

Andygray — Fri, 07 Sep 2018 15:20:04 GMT

Do you just have 3 vars v1-v3 or that could vary to many?

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 15:23:31 GMT

Many, unfortunately. Someone put the average of a state data for many variables to fill missing for each county/city. I don't want to use the average, so need to get rid of them!

Re: Replace ALL duplicate observations with blanks

novinosrin — Fri, 07 Sep 2018 15:36:31 GMT

data have;
input ID $ V1 V2 V3;
cards;
A  12 13 14
A 15 14 14
A 12 15 14
;
data _have;
set have;
array t v:;
grp=_n_;
do i=1 to dim(t);
_v=t(i);
__v=vname(t(i));
output;
end;
keep id _: grp;
run;
proc sort data=_have out=w;
by id __v _v;
run;
data _w;
set w;
by id __v _v;
if not(first._v and last._v) then call missing(_v);
run;

proc sort data=_w out=_w1;
by id grp;
run;
proc transpose data=_w1 out=want(drop=grp _:);
by id grp;
var _v;
id __v;
run;

Re: Replace ALL duplicate observations with blanks

Ksharp — Fri, 07 Sep 2018 15:36:38 GMT

Here is a Hash Table solution.

data have;
  input id $ v1 v2 v3;
  n+1;
datalines;
A  12 13 14
A 15 14 14
A 12 15 14
;
run;
proc transpose data=have out=temp;
by id n;
var v:;
run;
proc sort data=temp nouniquekey;
by id _name_ col1;
run;

data want;
 if _n_=1 then do;
   if 0 then set temp;
   declare hash h(dataset:'temp');
   h.definekey('id','_name_','col1');
   h.definedone();
 end;
set have;
array x{*} v:;
do i=1 to dim(x);
 _name_=vname(x{i});col1=x{i};
 if h.check()=0 then call missing(x{i});
end;
drop i n  _name_ col1;
run;


proc print;run;

Re: Replace ALL duplicate observations with blanks

rj438 — Fri, 07 Sep 2018 15:50:21 GMT

I think this was the quickest (albeit twisted) solution to my problem, thanks @novinosrin!