Organising table for a Forest Plot

[ammarhm]

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

[LinusH]

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

Take a look at PROC TABULATE and REPORT.

[ammarhm]

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

[ballardw]

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.

[Loko]

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;

[ammarhm]

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

[Loko]

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;

[ammarhm]

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

[Cynthia_sas]

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

[ammarhm]

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