PROC Transpose with missing value

[dera]

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

[V_27 · Accepted solution]

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;

[dera]

Thanks V_27! It works perfectly.

[V_27]

You are most welcome @dera 

[PeterClemmensen]

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;

[novinosrin]

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;




 

[FreelanceReinh]

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>.;