Turn my variable (column) into observation (row)

[MiguelCosta]

Hello everyone!

After some research I wasn't able to find a post with a similar problem so here it goes:

I have a dataset that looks like this:

It was collected in a longitudinal format with multiple Admissions ("Adm#1", "Adm#2", "Adm#3") following each other. I was hoping to look at each Admission individually, i.e. making Admission my observation/unit of analysis, regardless of the "Study ID". Ideally something that looks like this:

I have tried to recreate the dataset with TRANSPOSE but without success.

Thank you,

Miguel

[Reeza]

Transpose is the correct method, but you likely want to use a data step for your problem. 

Example 3 in the first link or Example 2 in the second is what you want. If you can't get it to work, please show the code you've tried. I'd definitely recommend the first link, easier to follow IMO and less steps. However, the second method generalizes more easily if the number of variables can change over time. 

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Edit: added the transpose option as well.

---

@MiguelCosta wrote:

Hello everyone!

After some research I wasn't able to find a post with a similar problem so here it goes:

I have a dataset that looks like this:

It was collected in a longitudinal format with multiple Admissions ("Adm#1", "Adm#2", "Adm#3") following each other. I was hoping to look at each Admission individually, i.e. making Admission my observation/unit of analysis, regardless of the "Study ID". Ideally something that looks like this:

I have tried to recreate the dataset with TRANSPOSE but without success.

Thank you,

Miguel

---

[yabwon]

Or, just for fun, with SQL:

data have;
	input studyid age bmi (adm_dt1-adm_dt3) (:yymmdd8.) (adm_dx1-adm_dx3) (:8.) 
		(adm_tx1-adm_tx3) (:8.);
	format adm_dt1-adm_dt3 yymmdd10.;
	datalines;
1 23 28.9 20180908 20181120 20190210 1 1 1 0 1 1
2 34 26.9 20170103 20171230 20180522 4 2 3 1 1 1
;
run;

proc sql;
create table want as
select studyid, age, bmi, adm_dt1 format yymmdd10., adm_dx1, adm_tx1 from have
union ALL
select studyid, age, bmi, adm_dt2, adm_dx2, adm_tx2 from have
union ALL
select studyid, age, bmi, adm_dt3, adm_dx3, adm_tx3 from have
;
quit;

All the best

Bart

[unison]

Maybe something like this will work for you:

data have;
	input studyid age bmi (adm_dt1-adm_dt3) (:yymmdd8.) (adm_dx1-adm_dx3) (:8.) 
		(adm_tx1-adm_tx3) (:8.);
	format adm_dt1-adm_dt3 yymmdd10.;
	datalines;
1 23 28.9 20180908 20181120 20190210 1 1 1 0 1 1
2 34 26.9 20170103 20171230 20180522 4 2 3 1 1 1
;
run;

data want;
	set have;
	array dt{3} adm_dt:;
	array dx{3} adm_dx:;
	array tx{3} adm_tx:;
	format admit_dt yymmdd10.;

	do i=1 to dim(dt);
		admit_dt=dt[i];
		admit_dx=dx[i];
		admit_tx=tx[i];
		output;
	end;
	drop adm_dt: adm_dx: adm_tx: i;
run;

[art297]

Not sure if you wanted to keep a variable indicating order but, regardless, the %untranspose macro can easily do what you want. e.g.:

data have;
	input studyid age bmi (adm_dt1-adm_dt3) (:yymmdd8.) (adm_dx1-adm_dx3) (:8.) 
		(adm_tx1-adm_tx3) (:8.);
	format adm_dt1-adm_dt3 yymmdd10.;
	datalines;
1 23 28.9 20180908 20181120 20190210 1 1 1 0 1 1
2 34 26.9 20170103 20171230 20180522 4 2 3 1 1 1
;
run;

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=have, out=want (drop=order), by=studyid age bmi,
  id=order, var=adm_dt adm_dx adm_tx)

Art, CEO, AnalystFinder.com