BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jordenlam
Calcite | Level 5

Hi,

 

It's kind of Transpose question.

 

Now I have data,

IDUnitNumOwnCar1OwnCar2OwnCar3
11213
12213
13213
2124 
2324 

 

I wonder how can I transpose to,

IDUnitNumOwnCar
112
121
133
212
234


Many thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20

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;

 

jordenlam
Calcite | Level 5

Yeah! It should be your assumption2. Many thanks!

novinosrin
Tourmaline | Level 20

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;

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 370 views
  • 0 likes
  • 2 in conversation