Help using Base SAS procedures

Organising table for a Forest Plot

Reply
Regular Contributor
Posts: 156

Organising table for a Forest Plot

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 1Header 2Header 3Header 4Header 8Header 9
1A

some data

some datasome datasome data
1Bsome datasome datasome datasome data
1Csome datasome datasome datasome data
2Dsome datasome datasome datasome data
2Esome datasome datasome datasome data
3Fsome datasome datasome datasome data
3Gsome datasome datasome datasome data
4Isome datasome datasome datasome data

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

Header 2Header 3Header 4Header 8Header 9
1....
   A

some data

some datasome datasome data
   Bsome datasome datasome datasome data
   Csome datasome datasome datasome data
2....
   Dsome datasome datasome datasome data
   Esome datasome datasome datasome data
3....
   Fsome datasome datasome datasome data
   Gsome datasome datasome datasome data
4....
   Isome datasome datasome datasome data

Could anyone please advice me on how to do it?

Best wishes

Super User
Posts: 5,260

Re: organising table rows/columns

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

Take a look at PROC TABULATE and REPORT.

Data never sleeps
Regular Contributor
Posts: 156

Re: organising table rows/columns

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

Super User
Posts: 10,552

Re: organising table rows/columns

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.

Super Contributor
Posts: 305

Re: organising table rows/columns

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;

Regular Contributor
Posts: 156

Re: organising table rows/columns

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

Super Contributor
Posts: 305

Re: organising table rows/columns

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;

Regular Contributor
Posts: 156

Re: organising table rows/columns

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

Attachment
SAS Super FREQ
Posts: 8,745

Re: organising table rows/columns

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

Regular Contributor
Posts: 156

Re: organising table rows/columns

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

Ask a Question
Discussion stats
  • 9 replies
  • 296 views
  • 5 likes
  • 5 in conversation