Hi everyone,
Is there a easy way to transpost my wide data into long format?
It's a pretty simple dataset which looks like this;
ID_mom ID_child ID_father year
111 444 777 2010
222 555 888 2011
333 666 999 2012
I want to transform it to long format, as:
ID
111
222
333
444
555
666
777
I have made an solution with below coding:
data id (keep ID);
set id_popb;
rename ID_MOM = ID;
run;
repeated for both child and father id
afterwards I append these three datafiles into one file.
But I really thin there's a smarter way to do this
Thank you in advance
Kind regards
Frank
Try this
Edited to not include Year variable.
data have;
input ID_mom ID_child ID_father year;
datalines;
111 444 777 2010
222 555 888 2011
333 666 999 2012
;
data want(keep = id);
set have;
array i id:;
do over i;
id = i;
output;
end;
run;
Try this
Edited to not include Year variable.
data have;
input ID_mom ID_child ID_father year;
datalines;
111 444 777 2010
222 555 888 2011
333 666 999 2012
;
data want(keep = id);
set have;
array i id:;
do over i;
id = i;
output;
end;
run;
Thank you very much.. It works fine!
My output contains rows with missing values. My first thought was I could add a where clause to the code: where ID ne "" .
Didnt work - therefore I added a short proc SQL afterward. Again I think my coding could be smarter.
It works - and I'm glad. Thanks again.
Kind regards Frank
The answer from @PeterClemmensen above does not provide the output you requested, where 222 was the 2nd row.
When you do this transformation, then you have no way of identifying that the 111 mom ID goes with the 444 child ID. Is that really what you want?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.