topic Re: Create Multiple Variables from Observations with a Common Identifier in SAS Programming

Create Multiple Variables from Observations with a Common Identifier

dgaribal — Tue, 15 Mar 2022 19:03:18 GMT

Hello,

I have a dataset that has lab draws over the span of a few days (example below). I want to create a dataset that has a unique line for each patient, with variables iterating _1, _2, etc. for each draw. I have tried to use an array, but am not very experienced with them so any help is much appreciated.

Thanks!

Dataset I currently have:

Patient	ALT	AST	INR
1	20	24	1.01
1	33	48	1.03
2	15	30	1.11
2	17	32	1.25
2	18	33	1.30

Dataset I want to have:

Patient	alt_1	ast_1	inr_1	alt_2	ast_2	inr_2	alt_3	ast_3	inr_3
1	20	24	1.01	33	48	1.03	.	.	.
2	15	30	1.11	17	32	1.25	18	33	1.30

Thanks again!

Re: Create Multiple Variables from Observations with a Common Identifier

PaigeMiller — Tue, 15 Mar 2022 19:16:21 GMT

Usually, turning a long data set to a wide data set is a poor choice, and harder to handle.

What analysis/table/report are you trying to create with this data?

Re: Create Multiple Variables from Observations with a Common Identifier

dgaribal — Tue, 15 Mar 2022 19:36:35 GMT

I completely agree. I am just adjusting the data to fit into a historic database that was created long before I was working on it, so to fit it in I unfortunately need to make it wide.

Re: Create Multiple Variables from Observations with a Common Identifier

Ksharp — Wed, 16 Mar 2022 12:02:52 GMT

data have;
input Patient ALT AST INR;
cards;
1 20 24 1.01
1 33 48 1.03
2 15 30 1.11
2 17 32 1.25
2 18 33 1.30
;

data temp;
 set have;
 by Patient;
 if first.Patient then n=0;
 n+1;
run;

proc transpose data=temp out=temp2;
by  Patient n;
var ALT AST INR ;
run;
proc transpose data=temp2 out=want(drop=_NAME_) delimiter=_;
by Patient ;
id _NAME_ n;
var col1;
run;

Re: Create Multiple Variables from Observations with a Common Identifier

dgaribal — Wed, 16 Mar 2022 16:13:16 GMT

This worked great! Thank you so much, I really appreciate it.