topic Re: Wide to long format in SAS Programming

Wide to long format

Kjeldsen89 — Fri, 10 Sep 2021 11:30:40 GMT

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:

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

Re: Wide to long format

PeterClemmensen — Fri, 10 Sep 2021 11:31:43 GMT

What about the Year values?

Do you want them too?

Re: Wide to long format

Kjeldsen89 — Fri, 10 Sep 2021 11:34:30 GMT

Nope

Re: Wide to long format

PeterClemmensen — Fri, 10 Sep 2021 11:36:22 GMT

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;

Re: Wide to long format

PaigeMiller — Fri, 10 Sep 2021 11:35:59 GMT

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?

Re: Wide to long format

Kjeldsen89 — Fri, 10 Sep 2021 11:58:46 GMT

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

Re: Wide to long format

PaigeMiller — Fri, 10 Sep 2021 12:40:18 GMT

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