topic Organising table for a Forest Plot in SAS Procedures

Organising table for a Forest Plot

ammarhm — Fri, 06 Mar 2015 11:17:07 GMT

Hi all

A small problem, not sure how to solve it (surely through proc sql...group by, but I cant figure it out, cant really figure out adding the extra row)

I have a table that looks like this:

Header 1	Header 2	Header 3	Header 4	Header 8	Header 9
1	A	some data	some data	some data	some data
1	B	some data	some data	some data	some data
1	C	some data	some data	some data	some data
2	D	some data	some data	some data	some data
2	E	some data	some data	some data	some data
3	F	some data	some data	some data	some data
3	G	some data	some data	some data	some data
4	I	some data	some data	some data	some data

I want to re-arrange Header 1 and Header 2:

Header 2	Header 3	Header 4	Header 8	Header 9
1	.	.	.	.
A	some data	some data	some data	some data
B	some data	some data	some data	some data
C	some data	some data	some data	some data
2	.	.	.	.
D	some data	some data	some data	some data
E	some data	some data	some data	some data
3	.	.	.	.
F	some data	some data	some data	some data
G	some data	some data	some data	some data
4	.	.	.	.
I	some data	some data	some data	some data

Could anyone please advice me on how to do it?

Best wishes

Re: organising table rows/columns

LinusH — Fri, 06 Mar 2015 11:25:02 GMT

This is not a table, it's a report. Therefore, don't use SQL.

Take a look at PROC TABULATE and REPORT.

Re: organising table rows/columns

ammarhm — Fri, 06 Mar 2015 11:26:42 GMT

Thank you

what I am showing here is actually a table in SAS, I need to reformat it to use in for further analysis

Best wishes

Re: organising table rows/columns

Loko — Fri, 06 Mar 2015 11:49:17 GMT

Hello,

data have;
input Header_1 Header_2 $;
datalines;
1 A
1 B
1 C
2 D
2 E
3 F
3 G
4 I
;

proc sort data=have;
by Header_1;
run;

data want;
set have;

by Header_1;
_Header_2=Header_2;

if first.Header_1 then do;Header_2=Header_1;output;end;

Header_2=_Header_2;
output;

drop Header_1 _Header_2;
run;

Re: organising table rows/columns

ammarhm — Fri, 06 Mar 2015 12:04:35 GMT

Thank you Loko

The code still duplicates some values, when the code selects the row value from the Header1, the rest of row is filled by data and not empty as per my example

any further suggestions?

Regards

Re: organising table rows/columns

Loko — Fri, 06 Mar 2015 16:05:47 GMT

Hello,

data have;
input Header_1 Header_2 $ Header_3 Header_4 $;
datalines;
1 A 2 a
1 B 3 s
1 C 4 a
2 D 4 a
2 E 54 a
3 F 23 g
3 G 24 a
4 I 24 g
;

proc sort data=have;
by Header_1;
run;

proc sql noprint;
SELECT "'"||trim(name)||"'", name INTO :all_vars separated by ',', :all_vars_miss separated by ','
FROM SASHELP.VCOLUMN
WHERE libname = upcase("work") AND memtype = 'DATA'
AND memname = upcase("have") and name not in ('Header_1', 'Header_2');
QUIT;

data want;
set have;

if _N_=1 then
do;
declare hash ha(dataset:'have',multidata:'Y');
ha.definekey('Header_1', 'Header_2');
ha.definedata(&all_vars);
ha.definedone();
end;

by Header_1;
_Header_2=Header_2;

if first.Header_1 then do;ha.add();put _all_;call missing(&all_vars_miss);Header_2=Header_1;output;end;

rc=ha.find(key:Header_1, key:_Header_2);
ha.clear();
Header_2=_Header_2;
output;

drop Header_1 _Header_2 rc;
run;

Re: organising table rows/columns

ballardw — Fri, 06 Mar 2015 17:54:52 GMT

Please give an example of an analysis that relies on the order of the variables in the data set, especially where the names of the variables don't change.

Re: organising table rows/columns

ammarhm — Sat, 07 Mar 2015 02:27:05 GMT

Thank you everyone for your suggestions, especially thank you Loko for your time. Unfortunatly the code is still not working the way I wanted it. There are missing values in the new table now

Let us give the full example, the attached table is a typical output table obtained by ods output from a proc logistic.

What I am trying to do is to convert the structure of the table to be compatible with the one needed to build a forest plot according to the excelent example given here

http://blogs.sas.com/content/graphicallyspeaking/2012/09/30/forest-plot-with-subgroups/

The exact code and the necessary table structure is available here:

http://blogs.sas.com/content/graphicallyspeaking/files/2012/09/ForestPlot_931.txt

As this is something I will be doing several times, I al trying to code the conversion of the OR table to the one needed for the forest plot.

I really appreciate all the help and suggestions

Best wishes

Re: organising table rows/columns

Cynthia_sas — Sat, 07 Mar 2015 04:52:24 GMT

Hi:

If you look at the program for SAS 9.3, you will see that the data is created with dots or periods as placeholders for the indents used in the final report. The dots are then replaced with the hex character for a non-breaking space.

You could take your data and generate the same type of data as used by the program using character functions and concatenate to make the sub-grouping variable.

cynthia

Re: organising table rows/columns

ammarhm — Sat, 07 Mar 2015 05:01:24 GMT

Thank you

However, if you look at the code you see that the table forest2 is created without dots etc, and that is the format I am trying to convert my table to

Best wishes