DATA Step, Macro, Functions and more

Transpose multiple paired columns into one for long data

Accepted Solution Solved
Reply
Super Contributor
Posts: 358
Accepted Solution

Transpose multiple paired columns into one for long data

Hi SAS enthusiasts,

 

I'd like to reorganize my data for proc mixed model. It's semi-long now. But I need 6 pairs of pre and post columns organized into single column per test such as (pre_post1, pre_post2 thru pre_post6) . Below is simple example. However, the data at the bottom is my actual data for the first two subjects.

 

data have;
input pat_id pre post;
cards;
3 648 750 
3 120 150
4 504 650
4 569 600
5 120 360
;

data proc_mixed;
input pat_id score group;
cards;
3 648 pre
3 120 pre
4 504 pre
4 569 pre
5 120 pre
3 750 post
3 150 post
4 650 post
4 600 post
5 360 post
;

 

data proc_mixed;
input TG	id	age_rz	GENDER	ETHNIC	hemog	PREFEV	PREFVC	PREFF	PREPF	POSFEV	POSFVC	POSFF	POSPF	PREFEVPP	PREFVCPP	POSFEVPP	POSFVCPP	wbc	agehome
;
cards; 
TG	id	age_rz	GENDER	ETHNIC	hemog	PREFEV	PREFVC	PREFF	PREPF	POSFEV	POSFVC	POSFF	POSPF	PREFEVPP	PREFVCPP	POSFEVPP	POSFVCPP	wbc	agehome
B	1	5	m	o	12.5	1.38	1.75	79	230	1.54	1.83	84	240	81	91	91	95	65	50
B	1	5	m	o		1.31	1.7	77	190	1.41	1.74	81	190	76	87	82	89		
B	1	5	m	o		1.44	1.76	82	210	1.56	1.81	86	255	80	86	87	89		
B	1	5	m	o		1.48	1.76	84	250	1.6	1.82	88	250	77	81	84	83		25
B	1	5	m	o		1.44	1.79	80	270	1.73	2.04	85	285	73	80	88	91		
B	1	5	m	o		1.73	2.07	84	270	1.82	2.13	85	280	81	84	85	87		30
B	1	5	m	o		1.85	2.35	79	300	2.07	2.47	84	305	83	93	93	97		
B	1	5	m	o		2	2.53	79	285	2.2	2.61	84	290	84	93	92	96		30
B	1	5	m	o		2.13	2.61	82	280	2.2	2.53	87	300	88	95	91	92		
B	1	5	m	o	13.4	2.24	2.87	78	345	2.39	2.87	83	400	86	97	92	97	77	20
B	1	5	m	o		2.68	3.35	80	355	2.7	3.23	84	380	85	91	86	88		
B	1	5	m	o		3.53	4.51	78	450	3.74	4.52	83	470	92	102	98	102		
B	1	5	m	o		3.86	4.95	78	455	4.06	5.07	80	540	93	103	98	105		
B	1	5	m	o		4.22	5.39	78	545	4.51	5.49	82	530	97	107	103	109		
B	1	5	m	o		4.35	5.61	78	545	4.62	5.74	80	600	96	107	102	109		
B	2	11	m	b	12.5	1.78	2.49	71	310	2.11	2.57	82	350	90	108	107	111	82	34
B	2	11	m	b		1.43	2.39	60	270	1.96	2.57	76	350	70	100	97	108		
B	2	11	m	b		1.57	2.4	65	280	1.98	2.54	78	380	76	100	96	105		
B	2	11	m	b		2.03	2.73	74	370	2.38	2.82	84	430	94	113	111	117		35
B	2	11	m	b		1.51	2.51	60	340	2.19	2.76	79	450	69	101	100	111		
B	2	11	m	b		2.3	3.01	76	360	2.64	3.16	84	480	92	107	106	112		30
B	2	11	m	b		2.75	3.21	86	435	3	3.38	89	420	108	111	118	117		
B	2	11	m	b		2.6	3.24	80	450	2.74	3.06	90	470	97	107	102	101		37
B	2	11	m	b		3.15	3.58	88	490	3.26	3.68	89	520	117	117	121	121		
B	2	11	m	b	12.5	2.12	3.32	64	370	2.57	3.43	75	450	75	104	91	107	62	20
B	2	11	m	b		2.81	3.43	82	425	3.13	3.63	86	510	99	107	111	113		
B	2	11	m	b		2.96	3.6	82	520	2.93	3.57	82	550	100	108	99	107		
B	2	11	m	b		2.86	4.07	70	490	3.34	4.06	82	550	94	119	110	119		
B	2	11	m	b		2.51	3.83	65	490	3.03	3.88	78	500	81	110	97	111		
B	2	11	m	b		3.1	3.84	81	490	3.42	3.74	91	530	96	107	106	104		
B	2	11	m	b		2.92	3.89	75	480	3.4	3.9	87	550	88	105	102	105
;

 


Accepted Solutions
Solution
Tuesday
Respected Advisor
Posts: 2,989

Re: Transpose multiple paired columns into one for long data

[ Edited ]

@Reeza is correct, if you are creating this data set, there are probably better ways to do this. However, if you have received the data set, then something like this, where to save myself some typing, I only show the code to convert two of your original variables.

 

data want;
    set proc_mixed;
    time='PRE';
    fev=prefev;
    fvc=prefvc;
 /* add more variables here */ 
    output;
    time='POS';
    fev=posfev;
    fvc=posfvc;
 /* add more variables here */ 
    output;
    keep tg--hemog time fev fvc /* add more variables here */ ;
run;

 

--
Paige Miller

View solution in original post


All Replies
Super User
Posts: 23,683

Re: Transpose multiple paired columns into one for long data

Are each measurements getting their own row, or are they staying separate? How did you build this data set, via a merge? 

 

Quickest way IMO is to use a data step. Your rename statement will be a bit longer but the general idea is the same. 

 

data want;
set have (in=H1 rename=pre=Value)
      have (in=H2 rename=post=Value);

length type $4.;

if H1 then type='Pre';
else if H2 then type='Post';

run;

 

 

Solution
Tuesday
Respected Advisor
Posts: 2,989

Re: Transpose multiple paired columns into one for long data

[ Edited ]

@Reeza is correct, if you are creating this data set, there are probably better ways to do this. However, if you have received the data set, then something like this, where to save myself some typing, I only show the code to convert two of your original variables.

 

data want;
    set proc_mixed;
    time='PRE';
    fev=prefev;
    fvc=prefvc;
 /* add more variables here */ 
    output;
    time='POS';
    fev=posfev;
    fvc=posfvc;
 /* add more variables here */ 
    output;
    keep tg--hemog time fev fvc /* add more variables here */ ;
run;

 

--
Paige Miller
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 87 views
  • 0 likes
  • 3 in conversation