topic Re: Merging and transposing rows in SAS Programming

Merging and transposing rows

ammarhm — Mon, 04 Apr 2016 04:52:16 GMT

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

Re: Merging and transposing rows

Reeza — Mon, 04 Apr 2016 04:57:57 GMT

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

Re: Merging and transposing rows

ammarhm — Mon, 04 Apr 2016 05:16:34 GMT

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

Re: Merging and transposing rows

ammarhm — Mon, 04 Apr 2016 05:52:34 GMT

Dear Reza

Here is a datafile, created manually

I hope this explains the idea further

Kind regards

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,

;;;;

Re: Merging and transposing rows

Kurt_Bremser — Mon, 04 Apr 2016 07:04:40 GMT

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;

Re: Merging and transposing rows

Ksharp — Mon, 04 Apr 2016 07:35:37 GMT


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;

Re: Merging and transposing rows

RW9 — Mon, 04 Apr 2016 08:20:03 GMT

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.

Re: Merging and transposing rows

ammarhm — Mon, 04 Apr 2016 08:56:28 GMT

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

Re: Merging and transposing rows

Kurt_Bremser — Mon, 04 Apr 2016 10:27:17 GMT

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