## Merging and transposing rows

Solved
Regular Contributor
Posts: 182

# Merging and transposing rows

[ Edited ]

Hi all

I have a file which I attached, basically containing a table with values for statistical analysis in rows per each covariate.

I am trying to build another table from this one which would contain one row, basicly combining the rows per covariate into a line

So for the covariate (white) for example, I wanted to have a row that looks like this:

(White) followed by next row = value from O10 for median (p25-p75) followed by p value (par_p1), then  value from O10 for mean(std), then value for (nonpar_p1)

Would really appreciate any help. Tried to solve it with arrays but I am not s good at arrays

Kind regards

Ammar

Accepted Solutions
Solution
‎04-04-2016 04:55 AM
Super User
Posts: 10,766

## Re: Merging and transposing rows

```
data have;
infile datalines dsd truncover;
input Covariate:\$13. Statistic:\$40. O10:\$8. pvalue:\$6.;
datalines4;
White,Median,22,0.1,
White,P25,5,0.1,
White,P75,66,0.1,
White,Mean,44,0.1,
White,Std,12,0.1,
Red,Median,8,0.05,
Red,P25,4,0.05,
Red,P75,12,0.05,
Red,Mean,9,0.05,
Red,Std,3,0.05,
Black,Median,4,0.3,
Black,P25,2,0.3,
Black,P75,5,0.3,
Black,Mean,4,0.3,
Black,Std,2,0.3,
Blue,Median,77,0.001,
Blue,P25,44,0.001,
Blue,P75,88,0.001,
Blue,Mean,70,0.001,
Blue,Std,10,0.001
;;;;
run;
proc sort data=have;by Covariate pvalue;run;
proc transpose data=have out=temp;
by Covariate pvalue;
var O10;
id Statistic;
run;
data want;
set temp;
length median_qrange Mean_std \$ 200;
median_qrange=cats(Median,'(',P25,'-',P75,')');
Mean_std=cats(Mean,'(',Std,')');
keep Covariate pvalue median_qrange Mean_std;
run;

```

All Replies
Super User
Posts: 23,683

## Re: Merging and transposing rows

Please post what you have and what you're trying to achieve. Ideally, you should post this as text into the forum, rather than as an attachment. I don't like downloading files.

Here's the instructions on how to create a data step of your data.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Regular Contributor
Posts: 182

## Re: Merging and transposing rows

Thank you Reza

I usually put it down as a table in the text but I cant seem to figure out how to put to put an inline table as the tool for the table insesion seems to have disappeard (?)

Thank you for your post regarding creating the data step, I am really not that expert with this kind of editing, I tried to look at the instruction but I got lost there...

Regards

Am

Regular Contributor
Posts: 182

## Re: Merging and transposing rows

[ Edited ]

Dear Reza

Here is a datafile, created manually

I hope this explains the idea further

Kind regards

Am

``````data have;
infile datalines dsd truncover;
input Covariate:\$13. Statistic:\$40. O10:\$8. pvalue:\$6.;
datalines4;
White,Median,22,0.1,
White,P25,5,0.1,
White,P75,66,0.1,
White,Mean,44,0.1.,
White,Std,12,0.1,
Red,Median,8,0.05,
Red,P25,4,0.05,
Red,P75,12,0.05,
Red,Mean,9,0.05,
Red,Std,3,0.05,
Black,Median,4,0.3,
Black,P25,2,0.3,
Black,P75,5,0.3,
Black,Mean,4,0.3,
Black,Std,2,0.3,
Blue,Median,77,0.001,
Blue,P25,44,0.001,
Blue,P75,88,0.001,
Blue,Mean,70,0.001,
Blue,Std,10,0.001

;;;;
``````

And here is the correspong code for the file I want:

``````data want;
infile datalines dsd truncover;
input Covariate:\$13. median_qrange:\$40. Mean_std:\$8. pvalue:\$6.;
datalines4;
White,22(5-66),44(12),0.1,
Red,8(4-12),9(3),0.05,
Black,4(2-5),4(2),0.3,
Blue,77(44-88),70(19),0.001,

;;;;
``````
Super User
Posts: 10,211

## Re: Merging and transposing rows

Try this:

``````data want;
format /* just for variable order */
Covariate
median_qrange
mean_std
pvalue
;
set have;
by Covariate notsorted;
retain median_qrange mean_std;
length
median_qrange \$40
mean_std \$8
;
if first.Covariate
then do;
median_qrange = '';
mean_std = '';
end;
prev_o10 = lag(O10);
if substr(median_qrange,length(median_qrange),1) = '(' then median_qrange = strip(median_qrange) !! strip(O10);
if Statistic = 'Median' then do;
median_qrange = strip(O10) !! '(';
end;
if Statistic = 'Mean'
then do;
median_qrange = strip(median_qrange) !! '-' !! strip(prev_o10) !! ')';
mean_std = trim(O10) !! '(';
end;
if last.Covariate
then do;
mean_std = trim(mean_std) !! trim(O10) !! ')';
output;
end;
keep Covariate median_qrange mean_std pvalue;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,599

## Re: Merging and transposing rows

You are designing clinical study outputs there, you may find the examples at PHUSE helpfull:
https://github.com/phuse-org/phuse-scripts/wiki/Standard-Script-Index

Also, I would expect that the company you work for already has a macro suite designed to do these types of outputs - that will be your first port of call.

Regular Contributor
Posts: 182

## Re: Merging and transposing rows

Thank you RW9
I am actually a physician doing my own stats, so I dont work for a company, but the link you gave is very useful indeed
Kind regards
AM
Solution
‎04-04-2016 04:55 AM
Super User
Posts: 10,766

## Re: Merging and transposing rows

```
data have;
infile datalines dsd truncover;
input Covariate:\$13. Statistic:\$40. O10:\$8. pvalue:\$6.;
datalines4;
White,Median,22,0.1,
White,P25,5,0.1,
White,P75,66,0.1,
White,Mean,44,0.1,
White,Std,12,0.1,
Red,Median,8,0.05,
Red,P25,4,0.05,
Red,P75,12,0.05,
Red,Mean,9,0.05,
Red,Std,3,0.05,
Black,Median,4,0.3,
Black,P25,2,0.3,
Black,P75,5,0.3,
Black,Mean,4,0.3,
Black,Std,2,0.3,
Blue,Median,77,0.001,
Blue,P25,44,0.001,
Blue,P75,88,0.001,
Blue,Mean,70,0.001,
Blue,Std,10,0.001
;;;;
run;
proc sort data=have;by Covariate pvalue;run;
proc transpose data=have out=temp;
by Covariate pvalue;
var O10;
id Statistic;
run;
data want;
set temp;
length median_qrange Mean_std \$ 200;
median_qrange=cats(Median,'(',P25,'-',P75,')');
Mean_std=cats(Mean,'(',Std,')');
keep Covariate pvalue median_qrange Mean_std;
run;

```
Super User
Posts: 10,211

## Re: Merging and transposing rows

Hmm, when I run your code my WANT dataset looks like this:

```                                                                      median_
Obs    Covariate    pvalue    qrange       Mean_std

1       Black      0.3       4(2-5)        4(2)
2       Blue       0.001     77(44-88)     70(10)
3       Red        0.05      8(4-12)       9(3)
4       White      0.1       22(5-66)      (12)
5       White      0.1.      (-)           44()
```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
🔒 This topic is solved and locked.