turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Merging and transposing rows

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-04-2016 12:49 AM - edited 04-04-2016 12:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ammarhm

04-04-2016 03:35 AM

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ammarhm

04-04-2016 12:57 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

04-04-2016 01:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

04-04-2016 01:24 AM - edited 04-04-2016 01:52 AM

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,
;;;;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ammarhm

04-04-2016 03:04 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ammarhm

04-04-2016 04:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-04-2016 04:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ammarhm

04-04-2016 03:35 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

04-04-2016 06:27 AM

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

Maxims of Maximally Efficient SAS Programmers