topic Re: Transposing blocks of observations in SAS Programming

Transposing blocks of observations

MiraKr_ — Fri, 02 Oct 2020 14:35:03 GMT

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)

Re: Transposing blocks of observations

ballardw — Fri, 02 Oct 2020 14:50:44 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Transposing blocks of observations

art297 — Fri, 02 Oct 2020 19:33:00 GMT

@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