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!
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;
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;
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;
Yeah! It should be your assumption2. Many thanks!
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;
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.
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.