Merging and transposing rows

[ammarhm]

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

[Reeza]

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...

[ammarhm]

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...

I appreciate your help and thank you for your kind reply

Regards

Am

[ammarhm]

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,

;;;;

[Kurt_Bremser]

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;

[RW9]

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.

[ammarhm]

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

[Ksharp · Accepted solution]

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;

[Kurt_Bremser]

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()