Hello,
I have a data set that looks like this and I want to transpose it to have only one observation for each ID.
id | col1 |
1 | 1 |
1 | 1 |
1 | 0 |
2 | 0 |
2 | 0 |
3 | 1 |
4 | . |
4 | . |
4 | 1 |
4 | 1 |
4 | 0 |
5 | 1 |
When I do PROC transpose with the code below, I get this:
proc transpose data=have out=want prefix = variable;
by id;
var col1;
run;
id | variable1 | variable2 | variable3 | variable4 | variable5 |
1 | 1 | 1 | 0 | . | . |
2 | 0 | 0 | . | . | . |
3 | 1 | . | . | . | . |
4 | . | . | 1 | 1 | 0 |
5 | 1 | . | . | . | . |
For ID 1 to 3 and 5, everything is okay but my question is for ID = 4. Is there a way to ignore the missing value in the the first two variables and assign variable 3 instead?
Here is what I want (I will eventually remove the variable that are completely empty)
id | variable1 | variable2 | variable3 | variable4 | variable5 |
1 | 1 | 1 | 0 | . | . |
2 | 0 | 0 | . | . | . |
3 | 1 | . | . | . | . |
4 | 1 | 1 | 0 | . | . |
5 | 1 | . | . | . | . |
Thanks
Hi @dera,
Try sorting id in ascending and col1 in descending order. Check the given sas code.
proc sort data=have out=new;
by id descending col1;
run;
proc transpose data=new out=want prefix=variable;
by id;
var col1;
run;
Hi @dera,
Try sorting id in ascending and col1 in descending order. Check the given sas code.
proc sort data=have out=new;
by id descending col1;
run;
proc transpose data=new out=want prefix=variable;
by id;
var col1;
run;
You are most welcome @dera
I think you have to perform the operation afterwards, like this
data have;
input id col1;
datalines;
1 1
1 1
1 0
2 0
2 0
3 1
4 .
4 .
4 1
4 1
4 0
5 1
;
proc transpose data=have out=want prefix = variable;
by id;
var col1;
run;
data final (drop=variable1-variable5 j i);
set want;
array in variable1-variable5;
array out $ var1-var5;
j=1;
do i=1 to 5;
if in(i) ne ' ' then do;
out(j)=in(i);
j+1;
end;
end;
run;
data have;
input id col1;
cards;
1 1
1 1
1 0
2 0
2 0
3 1
4 .
4 .
4 1
4 1
4 0
5 1
;
data want;
do until(last.id);
set have;
by id ;
array variable(5);
if first.id then call missing(of variable(*),_c);
if not missing(col1) then do;
_c+1;
variable(_c)=col1;
end;
end;
drop _: col1;
run;
Hello @dera,
What if the first COL1 value of ID 4 was not missing? Would you still ignore the missing value in the second observation of this ID?
If so, you could simply insert a WHERE statement into your PROC TRANSPOSE step, e.g.
where col1>.;
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.