Hi all,
I would like to join 3 variables in a data set which is measuring the same thing - hence I would only like a new variable with all the information from the columns and no duplicates.
The data I have:
ID 1 2 3
1 A A B
2 A B C
3 A A A
4 A B B
The data I want:
ID New_Var
1 A
1 B
2 A
2 B
2 C
3 A
4 A
4 B
If anyone can please help me, that'd be great.
Thanks in advance.
Questions:
Hi mkeintz, thanks for your reply.
1. No the column values are completely random, they are actually in the format of dates in the dataset I am using.
2. No the order of the output does not matter since I can sort them later - just need them all in one column.
3. Sorry do you mean this:
DATA Example;
format ID 1.
VAR1 VAR2 VAR3 $1.;
input ID VAR1 VAR2 VAR3;
datalines;
1 A A A
2 A B C
3 A A A
4 A B B
;
run;
Run a transpose:
proc transpose
data=example
out=want (rename=(col1=new_var))
;
by id;
var var:;
run;
There is a one-step solution, which benefits from the CALL SORTC subroutine:
DATA Example;
format ID 1. VAR1 VAR2 VAR3 $1.;
input ID VAR1 VAR2 VAR3;
datalines;
1 A A A
2 A B C
3 A A A
4 A B B
run;
data want (keep=id var);
set example;
array _v {*} dummy var1-var3;
call sortc(of _v{*});
do i=2 to dim(_v);
if missing(_v{i}) then continue;
var=_v{i};
if var^=_v{i-1} then output;
end;
run;
Hi @Bounce
You can try this code (proc transpose + proc sort to remove duplicate records):
proc transpose data=example out=want (rename=(col1=New_Var) drop=_name_);
by id;
var var:;
run;
proc sort data=want nodupkey;
by id New_Var;
run;
Output:
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.