Need help in creating a report using proc report

[sameer_123]

Hi All,

I am trying to create a new report using proc report.The dataset which i am using is like this

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12 GROUP_COL

I want to get the value of GROUP_COL at the top of the report and value of col12 at the bottom something like this

I am using thhis code 

proc report data=test nowd headskip out=work.data1;
col (group_col) (name col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12);
define group_col/ Group noprint;
define name / computed '' center ;

break before group_col / summarize style=[ font_weight=bold];
rbreak after / summarize;

compute name/char length=50;
if lowcase(_break_)='group_col' then name = group_col;
else name = '';

endcomp;

compute after group_col;
name=col12;
line ' ';
endcomp;

run;

Can anyone please help me out as to what changes should i make to get the desired o/p

[RW9]

Well, you've not posted test data (form of a datastep) so can't test, but I tend to do this:

proc sort data=work.data1 out=tmp nodupkey;
  by group_col col12;
run;
data _null_;
  set tmp;
  call execute(cats('title6 "',group_col,'";'));
  call execute(cats('footnote "',col12,'";'));
  call execute('proc report data=test nowd headskip out=work.data1;
                  column col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11;
                  where group_col="'||strip(group_col)||'" and col12="'||strip(col12)||'";
                  define col1 / order oder=data;
                run;');
run;

This generates one proc report step per distinct group_col/col12. You can generate titles/footnotes other text, apply coding, and such like really easy - its basically just changing the generated code.  Put your ODS statement before the data _null_ and close it afterwards, and all the proc report outputs will appear on their own pages in the file.

[sameer_123]

Hi,

Thanks for your solution which has started to make my output as i wanted.Still i need some fixes to the code specifically to make sure that the tables are starting from the same position in the report o/p.

I used this code for the generation of this output

proc sort data=work.import out=tmp ;
by group_col col12;
run;
options orientation=landscape;
ods listing;
ods escapechar='!';

data _null_;
set tmp;
call execute(cats('title6 j=l "',group_col,'" ;'));
call execute(cats('footnote j= l "',col12,'";'));
call execute('proc report data=tmp nowd headskip style=[rule=group frame=void] spanrows ;
column col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11;
where group_col="'||strip(group_col)||'" and col12="'||strip(col12)||'";
define col1/flow style(column)={width=1.0in};
define col2/flow style(column)={width=1.0in};
define col3/flow style(column)={width=1.0in};
define col4/flow style(column)={width=1.0in};
define col5/flow style(column)={width=1.0in};
define col6/flow style(column)={width=1.0in};
define col7/flow style(column)={width=1.0in};
define col8/flow style(column)={width=1.0in};
define col9/flow style(column)={width=1.0in};
define col10/flow style(column)={width=1.0in};
define col11/flow style(column)={width=1.0in};
run;');
run;

I have not used the nodupkey in the proc sort since the combination are not assumed to be always true.

Thnaks and Regards,

Sameer

[Cynthia_sas]

Hi,
You have almost the right idea. You don't want to use a compute block for name. Instead, you want to use another type of COMPUTE block...one for COMPUTE BEFORE _PAGE_ for your GROUP_COL. No need to assign the value to NAME, just use the variable value in a LINE statement.
 
cynthia

[sameer_123]

Hey Cynthia,

Thanks a ton for the shared code.I have been able to achieve this result using this code.

proc report data=import nowd headskip out=work.data1;
col group_col col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12;
define group_col/ order order =internal flow noprint;
define col12 / noprint flow;

rbreak after / summarize;
break after group_col / page summarize style=[ font_weight=bold];

compute before _page_/ style =header{just=left};
line group_col $100. ;
endcomp;

compute after _page_/style =header{just=Left };
length mybrk $1000.;
mybrk=col12;
lg=length(mybrk);
line mybrk $varying. lg;
line '';
endcomp;

run;

The o/p coming like this in the report

Basically i find three problems in the report output:

1.)Three tables should have been produced based on the Group_Col and Col12 but it has bifurcated the table only on the Group_col.

2.)A third table of blank value is created which is not what we wanted.

3.)All the tables being created should be of same size which i believe i could acheive through defining the specific column width for each column of report.

Thanks agin for all your help on this .

Thnaks and Regards,

Sameer

[Cynthia_sas]

Hi,
It looks like your COL1-COL11 fields are character. So you will want to remove the "summarize" from the BREAK AFTER statement (but leave the PAGE option). The summarize option is giving you a blank line underneath the last row of the table.

Then, that last empty table is coming from the RBREAK statement. You should remove that statement entirely--you have nothing to summarize at the end of the report. My example did have something to summarize.
My suggestion to determine why you have 2 tables instead of 3 tables is to reexamine your data and your log and make sure that you really have 3 distinct values for GROUP_COL. One way to find out is to do a PROC FREQ on GROUP_COL and make sure you have no MISSING values and the other way is to take the NOPRINT off the DEFINE statement for GROUP_COL (you can always put it back). If you ONLY have 2 unique values for GROUP_COL, you would NOT get 3 tables. I see that you are using GROUP_COL for the COMPUTE BEFORE. The first table has 1 row and the second table has 2 rows. Do you mean that the value of COL1 should be placed in a separate table -- in that case, GROUP_COL is not going to give you 3 tables if it only has 2 values. Which variable is controlling the number of tables? GROUP_COL by itself or GROUP_COL and COL1?

Another thing I would recommend before specifying column widths for everything would be to try this:
proc report data=xxx.yyy
style(report)={width=8in};

Which should force the entire table to be the same size. As a last resort, if that doesn't give the desired results, then use WIDTH= as a style override for every column.

BTW, when you are using ODS, options like FLOW are LISTING only options and have no impact on ODS output. So you may as well remove that option.

cynthia

[sameer_123]

Hi Cynthia,

Based on your suggestion which were very helpful,i was able to complete much of the output as expected.I used this code for my report generation.

data import ;
set test;
length key $300.;
key=cats(group_col,col1);
run;
options orientation=landscape NODATE nocenter
topmargin=.25in
bottommargin=.25in
leftmargin=.25in rightmargin=.25in ;
/*ods listing style =minimal;*/
ods escapechar='^';
ods pdf file="\\cis12019.ipsen-group.net\softs$\Citrix\Data\SAS_NDA_Test\FG Reports\Development\CIOMS II SUSAR
\CIOMS1.PDF";

Title1;
Title2;

proc report data=import nowd nowd headskip HEADLINE
style(column)={font_size=8pt just=C}
style=[rules=group frame=void] style(report)={width=10in} out=data1;
col group_col key col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12;
define group_col/ order order =internal flow noprint;
define col12 / noprint flow;
define key / order order =internal flow noprint;
define col12/noprint;
define col1/flow ;
define col2/flow ;
define col3/flow ;
define col4/flow ;
define col5/flow ;
define col6/flow ;
define col7/flow style(column)={width=1.0in};
define col8/flow ;
define col9/flow style(column)={width=1.0in};
define col10/flow style(column)={width=1.0in};
define col11/flow style(column)={width=1.0in};

break after key / page summarize;
compute before _page_/ style =header{just=left};
line group_col $100. ;
endcomp;
compute after _page_/style =header{just=Left };
length mybrk $1000.;
mybrk=col12;
lg=length(mybrk);
line mybrk $varying. lg;
line '';
endcomp;
run;

The only problem is that the code is printing based on the combination of group_col and col1 ,one page per combination.This is somewhat defeating the purpose of the report as it is making it very large.

What infact i wanted it to print the tables once the first table is finished.

So for every new combination should get printed exactly after the previous combination has been printed.

Secondly its is possible to print the header as presented in the image 

I was thinking of using Title to print this info but if i use titke1 twice the second call overrides the information from the second one.

[Cynthia_sas]

Hi: As I explained previously, some of your options are LISTING only options and irrelevant to using ODS PDF. The use of FLOW and HEADLINE and HEADSKIP just make your code harder to read and distracting because they are not applicable at all to ODS PDF.

I do not completely understand what you mean when you say: "The only problem is that the code is printing based on the combination of group_col and col1 ,one page per combination.This is somewhat defeating the purpose of the report as it is making it very large. What in fact i wanted it to print the tables once the first table is finished. So for every new combination should get printed exactly after the previous combination has been printed."

If you are using ODS PDF, have you tried the STARTPAGE=NO option that goes on the ODS PDF or ODS RTF top statement?

You indicated that you were thinking of using the TITLE to print "this info" -- what info. What you show in your screen shot seems to be a combination of TITLES and COMPUTE BEFORE _PAGE_ information. And, yet, I do not see any TITLES in your posted code. How did you generate the screen shot that you sent?

cynthia

Since you did not post any data and since I did not understand what you were doing with group_col and col1 and col12, I just mocked up two possibilities using SASHELP.CLASS and hardcoded the COMPUTE BEFORE _PAGE_ strings. I used STYLE=JOURNAL. If you need more in-depth help, you might want to work with Tech Support on this.

[sameer_123]

Hi Cynthia,

Thanks for posting the code which helped me a lot.I have almost achieved the o/p i need for my report.

I have one last hurdle left though.What i want in my report is two titles basically.The address title which is left aligned and the centre title which should be bold as decipted in the image below.

i am using this code to generate the title but the the first title in the centre is getting wrapped which 

options orientation=landscape NODATE nocenter linesize=max
/*topmargin=.25in 
bottommargin=.25in
leftmargin=.25in rightmargin=.25in*/ ;
/*ods listing  style =minimal;*/
ods escapechar='~';  
ods pdf file="\\cis12019.ipsen-group.net\softs$\Citrix\Data\SAS_NDA_Test\FG Reports\Development\CIOMS II SUSAR
\CIOMS1.PDF" startpage=no uniform;

    
title1 j=c height=7.5pt  "Medically Confirmed Serious Reactions and Non-serious Unlisted Reactions"
 j=l font=b 'Ipsen Biophar Limited' ; 

 title2 j=c  height=7.5pt "Initial Received Date Between : &dat1 -&date2"
 j=l font=b '190 Bath Road';

 title3  j=c height=7.5pt "Suspect Drug=&Drug"
 j=l font=b 'Slough	,SL1 3XE';
 footnote;


proc report data=import nowd nowd headskip HEADLINE split='*' 
style=[rules=group frame=void] 
style(column)={font_size=6pt just=c} 
style(header)={font_size=6pt just=L}
style(report)={width=10in cellpadding=1pt cellspacing=.10pt font_size=9pt}  ;
col group_col key col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12;
define group_col/ order order =internal flow noprint style(column)={cellwidth=9.0in};
define col12 / noprint flow;
define key / order order =internal flow noprint;
 define col12/noprint;
                  define col12/noprint;
                  define col1/flow "File No.   (Version Number)" contents="',col1,'" page;
                  define col2/flow "Country"  "Source";
                  define col3/flow "Age"       "Gender";
                  define col4/flow "Primary Product Name";
                  define col5/flow "Dose"  "(Frequency)" "*" "Dates of Therapy" "First/Last";
                  define col6/flow style(column)={cellwidth=1.0in} "Date of Event Onset" "First Dose Latency" "Last Dose Latency" "Dechallenge   " "Rechallenge   ";
                  define col7/flow style(column)={width=1.0in} 	"Reaction" "MedDRA PT decode";
                  define col8/flow "Co-manifestation(s)" "*" "(Company Seriousness)" "Reported Serious Criteria" "Labelling";
                  define col9/flow style(column)={width=1.0in} "Outcome";
                  define col10/flow style(column)={width=1.0in} "Reporter Causality"  "(Company Causality)";
                  define col11/flow style(column)={cellwidth=1.0in} "Product Indication";

break after key / page summarize;
compute before _page_/ style =[just=left ];
line '';/*
line '________________________________________________________________________________________________________________________________________________________________';
line ' ';*/
line group_col $100. ;/*
line ' ';
line '________________________________________________________________________________________________________________________________________________________________';
*/endcomp;
compute after _page_/style =[just=left ];
length mybrk $1000.;
mybrk=col12;
lg=length(mybrk);
line "Summary Description";
line mybrk $varying. lg;
line '';
endcomp;
run;

My second problem is that when i uncomment the line statement in this block,the whole structure of the report is broken

break after key / page summarize;
compute before _page_/ style =[just=left ];
line '';/*
line '________________________________________________________________________________________________________________________________________________________________';
line ' ';*/
line group_col $100. ;/*
line ' ';
line '________________________________________________________________________________________________________________________________________________________________';
*/endcomp;

Basically i want to have two lines just before an new group col .

Thnaks in advance.

[Cynthia_sas · Accepted solution]

Hi,
As I show in my example, you have to use the J=L and J=C to achieve the address and title as you want. I believe that when you use those methods in the title statement that the title line gets divided into 3 sections. I do not know whether it is possible to change the amount of space allocated to the center section to avoid wrapping. This is a question for Tech Support.

And, you might want to work with Tech Support on your GROUP_COL question too. You have not posted data and without data, nobody else can test your program. Since it is your data values causing the break issues you see, no one can really help you without seeing the data. Since it seems you cannot post the data, it is nearly impossible to help you with just the copy of the code you keep posting.

If you compare my TITLE statement to your TITLE statement and my output to your output, you will see that it IS possible to get the TITLE area you want, but as you have experienced, the center TITLE area has a restricted size using this method and that restricted size for the center title can cause wrapping. If there's a workaround for this behavior, you need to check with Tech Support.

In addition, if you compare my code to your code, you will notice that I did not use a LINE statement of underscores for the COMPUTE BEFORE _PAGE_ text. That technique (to use a keyboard character for underlining) will not work with ODS destinations, because that was an old LISTING only technique that is irrelevant in ODS destinations.

So, it seems to me, with your persistence in using FLOW and HEADLINE and HEADSKIP and a LINE statement of underscore characters that you keep trying LISTING type techniques for ODS PDF which is not a reasonable thing to keep trying, because, as you note, ODS PDF does not use the LINE of underscores in any useful way.

I can't help you with the LINE statement method for ODS PDF because it just won't work the way you envision. In my #2 code, in the screen shot I posted, I show the use of the style(header) attribute change:
proc report data=sashelp.class
style(report)={width=60%}
style(header)={bordertopcolor=black bordertopwidth=1pt};

to put a divider line on "top" of the column headers (which is underneath the COMPUTE text). By comparison, my #1 example in the screen shot shows the ODS method of doing an underline only under the text.

I do not know of any other methods that will work with ODS PDF, but I know that your LINE statement approach is not appropriate for ODS PDF. This is why I recommend that you work with Tech Support.

cynthia

[sameer_123]

Hi Cynthia,

I have been able to control the wrapping .You were right when you said that the title area was getting divided into two areas when i was trying to use two titles into a single one by using centre and left alignment respectively.

This paper http://support.sas.com/kb/24/492.html explains the idea and workaround pretty cleanly.Thanks all for your help here .

Thanks and Regards,

Sameer