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;
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.