DATA Step, Macro, Functions and more

Merging and transposing rows

Accepted Solution Solved
Reply
Regular Contributor
Posts: 164
Accepted Solution

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,044

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;


View solution in original post


All Replies
Super User
Posts: 19,862

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: 164

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

I appreciate your help and thank you for your kind reply

Regards

Am

Regular Contributor
Posts: 164

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: 7,854

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
Super User
Super User
Posts: 7,988

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: 164

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,044

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: 7,854

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 481 views
  • 2 likes
  • 5 in conversation