BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ammarhm
Lapis Lazuli | Level 10

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

8 REPLIES 8
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10
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
Super User

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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