topic Re: Removing duplicate values in SAS Programming

Removing duplicate values

deega — Fri, 10 Feb 2017 06:32:43 GMT

Dear Members,

Once again, I need your expert advice.

I have the following SAS Dataset (posting the dummy data reproduced in excel).

ID	A1	A2	A3	A4	A5
1234	abc	def	abc	def
1235	abc	abc	abc	abc
1236	abc
1240	asf	abc	asf	def	asf
1245	abc	def	asf	ghi	jkl
1251	asf	abc
1252	abc	abc

I am looking for the following output where all the duplicate values across rows will be deleted.

ID	A1	A2	A3	A4	A5
1234	abc	def
1235	abc
1236	abc
1240	asf	abc	def
1245	abc	def	asf	ghi	jkl
1251	asf	abc
1252	abc

Thanks in advance !

Re: Removing duplicate values

user24feb — Fri, 10 Feb 2017 07:48:56 GMT

For your example:

Data A;
  Input ID A1 $ A2 $ A3 $ A4 $ A5 $;
  Datalines; 
1234 abc def abc def .
1235 abc abc abc abc .
1236 abc . . . .
1240 asf abc asf def asf
1245 abc def asf ghi jkl
1251 asf abc . . .
1252 abc abc . . .
; 

Data B1 (Drop=i j);
  Set A;
  Array A{*} A:;
  Do i=1 To Dim(A);
	Do j=i To Dim(A);
	  If (A{j}=A[i}) & (i^=j) Then Call Missing (A{j});
	End;
  End;
Run;

Re: Removing duplicate values

user24feb — Fri, 10 Feb 2017 07:54:14 GMT

.. this one also shifts ..

Data B2 (Drop=i j);
  Set A;
  Array A{*} A:;
  Do i=1 To Dim(A);
	Do j=i To Dim(A);
	  If (A{j}=A[i}) & (i^=j) Then Call Missing (A{j});
	End;
  End;
  Do i=1 To Dim(A);
    If Missing (A{i}) Then Do j=i+1 To Dim(A);
	  If not Missing (A{j}) Then Do;
		A{i}=A{j};
		Call Missing (A{j});
	  End;
	End;
  End;
Run;

Re: Removing duplicate values

RW9 — Fri, 10 Feb 2017 09:34:24 GMT

Please post test data in the form of a datastep. See how I have put the test data in the program below.

As an alternative to the datastep you could normalise, sort and transpose:

data have;
  infile datalines missover;
  input ID	A1 $	A2 $	A3 $	A4 $	A5 $;
datalines;
1234	abc	def	abc	def	　
1235	abc	abc	abc	abc	　
1236	abc	　	　	　	　
1240	asf	abc	asf	def	asf
1245	abc	def	asf	ghi	jkl
1251	asf	abc	　	　	　
1252	abc	abc	　
;
run;

proc transpose data=have out=inter;
  by id;
  var a:;
run;

proc sort data=inter nodupkey;
  by id col1;
  where col1 ne "";
run;
 
proc transpose data=inter out=want prefix=a;
  by id;
  var col1;
run;