topic Re: Turn my variable (column) into observation (row) in New SAS User

Turn my variable (column) into observation (row)

MiguelCosta — Sun, 19 Apr 2020 18:41:05 GMT

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

Re: Turn my variable (column) into observation (row)

Reeza — Sun, 19 Apr 2020 18:54:33 GMT

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

Re: Turn my variable (column) into observation (row)

unison — Sun, 19 Apr 2020 19:15:27 GMT

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;

Re: Turn my variable (column) into observation (row)

yabwon — Sun, 19 Apr 2020 19:35:41 GMT

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

Re: Turn my variable (column) into observation (row)

art297 — Sun, 19 Apr 2020 20:50:11 GMT

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