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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.