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
;
@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;
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;
@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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.