topic Need help in creating report using Tabulate in ODS and Base Reporting

Need help in creating report using Tabulate

sameer_123 — Thu, 08 Sep 2016 12:50:52 GMT

Hi All,

I am trying to create a report using proc tabulate

option missing=0 nonumber nodate ;
options orientation=landscape;
ods listing;
ods escapechar='^';
ods pdf file="\\ipsen-group.net\citrix\Data\SAS_NDA_TEST\FG Reports\Output\SAE_Tabulation_Clinical_Trial_&file_app..pdf";
Title1 J=C  font=courier "Cumulative tabulation of serious adverse events from clinical trials"  ;
Title2 j=center font=courier "&date_type_disp1 from &cum_strt_dt to &cum_end_dt" ;
Title3 j=c font=courier "&rpt_hdr_sd_disp";
Title4 j=c font=courier "&rpt_hdr_prot_disp";

footnote justify=center 'Page ^{thispage}'; 

PROC TABULATE DATA=FINAL;
VAR COUNT / STYLE=[textalign=center];
CLASS SOC MEDDRA_PT PRODUCT;
TABLE SOC=''*(MEDDRA_PT=''/ box='MEDDRA_PT' ALL=''), (PRODUCT='' ALL='Total')*COUNT=''*sum=''*F=6.;
RUN;

Now the code is giving me two issues.

MedDRA_PT is getting repeated which i don't want.i want the labels SOC and MEDDRA_PT at the start of the
report in coulmn headings.
Other issue is i want the sum row to be shifted to the top.Instead of coming at the last in the group, i want this
to be shifted at the start of teh group of SOC.
Please advice me how i can achieve this.

Re: Need help in creating report using Tabulate

Ksharp — Thu, 08 Sep 2016 13:29:54 GMT

I think you need calculated these value before using proc tabulate.


proc sql;
create table class as
select sex,age,sum(weight) as weight,sum(height) as height
 from sashelp.class
  group by sex,age
union all 
select sex,.,sum(weight),sum(height)
 from sashelp.class
  group by sex;
quit;

proc tabulate data=class;
class sex age/missing;
var weight height;
table sex*age,weight height/ misstext=' ' ;
keylabel sum=' ';
run;

Re: Need help in creating report using Tabulate

ballardw — Thu, 08 Sep 2016 14:29:05 GMT

To address your last issue first. When you use the ALL instruction the order in the syntax determines the location of the summary.

So (Product All) says to put the summary at the end (right) of the products. Change the order to (All Product) to have the summary appear before (left of) the Products.

Tabulate doesn't do a good spanning header in the box.

I also think the example syntax you posted didn't create the example text as your location of BOX is incorrect and should generate an error message.

This may help a bit for your table statement

TABLE SOC=''*(ALL='' MEDDRA_PT='' ), 
      (ALL='Total'PRODUCT='' )*COUNT=''*sum=''*F=6.
      / box='SOC                  MEDDRA_PT';

You may want to investigate use of CLASSLEV options for SOC and MEDDRA_PT to controll the width of the column so the Box= text aligns better.

Re: Need help in creating report using Tabulate

sameer_123 — Thu, 08 Sep 2016 16:48:37 GMT

Hi,

Thanks for your detailed and simple answer.The box option is working and i will investigate further about the classlev option to control the alignment.

Regarding the alignment of the summary result,actually i want the results to be printed at the start of every new soc value.So rather than moving the display to left or right, i want it to move it to the top from its current position which is at the bottom.I have shown this by teh arrow.

Re: Need help in creating report using Tabulate

ballardw — Thu, 08 Sep 2016 18:01:40 GMT

Did you note this change to your code in my response:

(ALL='Total'PRODUCT='' )

Moving the ALL to before the Product means the summary occurs before the product categories (above in this case)