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 !
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;
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;
.. 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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.