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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

What about the Year values?

 

Do you want them too?

Kjeldsen89
Calcite | Level 5
Nope
PeterClemmensen
Tourmaline | Level 20

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;
Kjeldsen89
Calcite | Level 5

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 

PaigeMiller
Diamond | Level 26

The answer from @PeterClemmensen above does not provide the output you requested, where 222 was the 2nd row.

--
Paige Miller
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1219 views
  • 0 likes
  • 3 in conversation