topic Re: Transpose Question in SAS Procedures

Transpose Question

jordenlam — Mon, 26 Aug 2019 17:16:51 GMT

Hi,

It's kind of Transpose question.

Now I have data,

ID	UnitNum	OwnCar1	OwnCar2	OwnCar3
1	1	2	1	3
1	2	2	1	3
1	3	2	1	3
2	1	2	4
2	3	2	4

I wonder how can I transpose to,

ID	UnitNum	OwnCar
1	1	2
1	2	1
1	3	3
2	1	2
2	3	4

Many thanks!

Re: Transpose Question

novinosrin — Mon, 26 Aug 2019 17:26:09 GMT

Hi @jordenlam i am going with my assumption

Are you sure about

2 3 4 result(the last one)

data have;
infile cards truncover;
input ID	UnitNum	OwnCar1	OwnCar2	OwnCar3;
cards;
1	1	2	1	3
1	2	2	1	3
1	3	2	1	3
2	1	2	4	. 
2	3	2	4	.
; 

data want;
set have;
array t owncar1-owncar3;
OwnCar=t(unitnum);
run;

Re: Transpose Question

novinosrin — Mon, 26 Aug 2019 17:48:32 GMT

If not the simple above, then my assumption2

data have;
infile cards truncover;
input ID	UnitNum	OwnCar1	OwnCar2	OwnCar3;
cards;
1	1	2	1	3
1	2	2	1	3
1	3	2	1	3
2	1	2	4	. 
2	3	2	4	.
; 

proc transpose data= have out=temp(drop=_name_ where=(owncar ne .) rename=(col1=owncar));
by id unitnum;
var owncar:;
run;
data want;
do until(last.id);
k+1;
 do _iorc_=1 by 1 until(last.unitnum);
  set temp;
  by id unitnum;
  if _iorc_=k then output;
 end;
end;
k=.;
drop k;
run;

Re: Transpose Question

jordenlam — Mon, 26 Aug 2019 17:52:22 GMT

Yeah! It should be your assumption2. Many thanks!

Re: Transpose Question

novinosrin — Mon, 26 Aug 2019 19:00:15 GMT

Hello @jordenlam how about this?

data have;
infile cards truncover;
input ID	UnitNum	OwnCar1	OwnCar2	OwnCar3;
cards;
1	1	2	1	3
1	2	2	1	3
1	3	2	1	3
2	1	2	4	. 
2	3	2	4	.
; 

data want;
 do _n_=1 by 1 until(last.id);
 set have;
 by id;
 array t(*) OwnCar1	OwnCar2	OwnCar3;
 OwnCar=t(_n_);
 output;
 end;
 keep  id unitnum owncar;
run;