Hello,
I trying to convert table "Have" into table "Want". I have multiple tables "Have" with different number of steps in them and looking for an automated way to determine the number of steps.
Table "Have" | |||
step | Variable | Estimate | Odd_Ratio |
0 | INTERCEPT | 2.2168 | 9.178 (2.62, 32.149) |
0 | PRIOR_CSA | -0.6381 | 0.528 (0.236, 1.18) |
0 | MALE | -0.4896 | 0.613 (0.319, 1.176) |
0 | EASI0141 | -0.00831 | 0.992 (0.966, 1.018) |
0 | AAGE | 0.017 | 1.017 (0.993, 1.042) |
0 | EASI0142 | 0.00382 | 1.004 (0.96, 1.05) |
1 | INTERCEPT | 2.2368 | 9.363 (2.737, 32.037) |
1 | PRIOR_CSA | -0.6275 | 0.534 (0.241, 1.182) |
1 | MALE | -0.4894 | 0.613 (0.32, 1.176) |
1 | EASI0141 | -0.00638 | 0.994 (0.98, 1.008) |
1 | AAGE | 0.017 | 1.017 (0.993, 1.042) |
2 | INTERCEPT | 1.9037 | 6.711 (2.531, 17.794) |
2 | PRIOR_CSA | -0.7005 | 0.496 (0.228, 1.081) |
2 | MALE | -0.5303 | 0.588 (0.309, 1.122) |
2 | AAGE | 0.018 | 1.018 (0.994, 1.043) |
Desired format:
Table "Want" | ||||||||
step | Variable | Estimate_0 | Odd_Ratio_0 | Estimate_1 | Odd_Ratio_1 | Estimate_2 | Odd_Ratio_2 | ProbChiSq_2 |
0 | INTERCEPT | 2.2168 | 9.178 (2.62, 32.149) | 2.2368 | 9.363 (2.737, 32.037) | 1.9037 | 6.711 (2.531, 17.794) | 0.0001 |
0 | PRIOR_CSA | -0.6381 | 0.528 (0.236, 1.18) | -0.6275 | 0.534 (0.241, 1.182) | -0.7005 | 0.496 (0.228, 1.081) | 0.0776 |
0 | MALE | -0.4896 | 0.613 (0.319, 1.176) | -0.4894 | 0.613 (0.32, 1.176) | -0.5303 | 0.588 (0.309, 1.122) | 0.1072 |
0 | EASI0141 | -0.00831 | 0.992 (0.966, 1.018) | -0.00638 | 0.994 (0.98, 1.008) | 0.018 | 1.018 (0.994, 1.043) | 0.1364 |
0 | AAGE | 0.017 | 1.017 (0.993, 1.042) | 0.017 | 1.017 (0.993, 1.042) | |||
0 | EASI0142 | 0.00382 | 1.004 (0.96, 1.05) |
Since your example data only shows one set of values for Step=0 and Variable=Intercept you have to provide some details as to
1) Where do the values Estimate_1, Estimate_2 (and the other _1 _2) values come from
2) where does the ProbChisqr_2 come from since it is not in the input data.
Does the "step" actually have any meaning because apparently you might be combining values from different step values but labeling all as step 0.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.
This would likely be a tad easier if you had the "odds ratio" as three separate numeric values instead of that character value.
@MiraKr_ ,
You could do this using proc transpose, but I find the transpose macro makes it easier when you are transposing more than one variable.
The following code will accomplish what you want:
data need;
set have;
if variable='INTERCEPT' then order=1;
else if variable eq 'PRIOR_CSA' then order=2;
else if variable eq 'MALE' then order=3;
else if variable eq 'EASI0141' then order=4;
else if variable eq 'AAGE' then order=5;
else if variable eq 'EASI0142' then order=6;
run;
proc sort data=need;
by order step;
run;
filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;
%transpose(data=need, out=want, by=variable, id=step,
delimiter=_, var=Estimate Odd_Ratio)
HTH,
Art
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.