BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
deega
Quartz | Level 8

Dear Members,

Once again, I need your expert advice.

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

IDA1A2A3A4A5
1234abcdefabcdef 
1235abcabcabcabc 
1236abc    
1240asfabcasfdefasf
1245abcdefasfghijkl
1251asfabc   
1252abcabc   

 

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

 

IDA1A2A3A4A5
1234abcdef   
1235abc    
1236abc    
1240asfabcdef  
1245abcdefasfghijkl
1251asfabc   
1252abc    

 

Thanks in advance !

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

View solution in original post

3 REPLIES 3
user24feb
Barite | Level 11

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;
user24feb
Barite | Level 11

.. 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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1178 views
  • 2 likes
  • 3 in conversation