SAS Proc report how to merge similar rows in ods

[Arora_S]

I am trying to create a report in pdf using proc report from a dataset and out put looks like below in a pdf document :

patid	name	gender
100	abc	m
relation_name	date	counts
dad	8/19/2016	1
dad	8/19/2016	1

But I want to supress the relation_name section in one for repeats so output looks as below :

patid	name	gender
100	abc	m
relation_name	date	counts
dad	8/19/2016	1

I am doing a group by patid but its not working, tried grouping by all columns also but dad information is repeating.

Any suggestions on this issue are appreciated .

[Cynthia_sas]

Hi: What PROC REPORT code did you try? You did not post any code or data or show any PDF output. What you posted is NOT PDF output -- it looks like text output pasted into the forum. You can insert screen shots and code in Forum postings.

Do you have a DEFINE statement for PATID or for RELATION_NAME? It appears that you might have a BREAK statement? and you are asking whether it is possible to suppress a line if the count is 1 (the answer is no).

But, without seeing your data or ALL your code that you've tried, it is hard to make a constructive comment on what you asked.

cynthia

[Arora_S]

Ok, please find more clarification for question asked,

My input data looks like this(Also attached csv file) :

cust_name	sex	marital	case_number	lot_num	OI_Date	client_name	client_num
john	Male	married	12345678	1234	8/19/2016	john	1
john	Male	married	12345678	1234	8/19/2016	wilson	2
john	Male	married	12345678	1234	8/19/2016	mary	3
john	Male	married	12345678	1234	8/19/2016	nirvana	4
john	Male	married	12345678	5678	8/19/2016	gatsy	5
john	Male	married	12345678	5678	8/19/2016	cassy	6
john	Male	married	12345678	5678	8/19/2016	cassida	7
john	Male	married	12345678	5678	8/19/2016	tim	8
john	Male	married	12345678	5678	8/19/2016	rob	9

I used the proc report code as below and ods options , code is as follows:--->

ods results;
OPTIONS orientation=landscape   NONUMBER NODATE LS=248 PS =256 COMPRESS=NO   MISSING = ' '
topmargin=.25in bottommargin=.25in leftmargin=.50in rightmargin=.50in  nobyline  papersize=a4;
ods escapechar='^';

ods pdf file = "C:\Users\xyz\Documents\Report\rpt.pdf" style=styles.listing notoc uniform ;

TITLE1 bold f= Calibri h=14pt wrap j=c "REPORT: LOT"             ;
TITLE2  f= Calibri j=C h=12pt  "Lot: " f= Calibri h=12pt   "#byval(lot_num)"        ;
TITLE3 " ";
proc report nofs  data = demo nowd spanrows ls =256  split='~' headline missing
style(header)={just=left CELLHEIGHT=12pt  font_weight=bold font_face="Calibri"  font_size = 11pt BORDERSPACING=8pt   cellspacing=2pt cellpadding=9pt
 borderbottomcolor=black  cellwidth=50%  borderbottomwidth=2 BORDERWIDTH=5pt rules=cols bordertopcolor=black
BORDERBOTTOMWIDTH=0.25pt BORDERTOPWIDTH=0.20pt  BORDERCOLORLIGHT= BLACK FRAMEBORDER=ON   frame=above WHITESPACE=pre_line }
style(report)={font_size=10pt cellpadding=1.0
 just=left cellspacing=0 rules=none font_face="Calibri" frame = void vjust = middle  }
style(column)={font_size=7pt vjust = middle WHITESPACE=pre_line } ;
by lot_num ;
columns  cust_name    sex    marital    case_number    OI_Date    client_name    client_num
    ;
define  cust_name /group page   'CUST NAME' left style = [cellwidth=40mm   rules=none ];
define  sex     / group  'GENDER' format = $gender. center  style(column)={just=center cellwidth=20mm rules=none }
style(header)={just=center cellwidth=20mm rules=none};
define  marital     / group 'MARITAL' center  format = $mar. style(column)={just=center cellwidth=25mm rules=none }
style(header)={just=center cellwidth=25mm rules=none};
define  case_number     / group 'CUST NUM' center format =mmddyy10. style(column)={just=center cellwidth=25mm rules=none }
style(header)={just=center cellwidth=25mm rules=none} ;
define  OI_Date/ GROUP 'TRANS DATE' left format=mmddyy10.  style(column)={just=center cellwidth=25mm rules=none }
style(header)={just=center cellwidth=25mm rules=none};

define  client_name    / GROUP 'CLIENT NAME' left   style = [cellwidth=75mm   rules=none];
define  client_num / GROUP 'CLIENT NUM' left style = [cellwidth=65mm   rules=none];

break after cust_name / PAGE  ;
compute before _page_ / style = {just = l};
endcomp;
/*COMPUTE after;*/
/*LINE "________________________________________________________________________________________________________________________________________";*/
/*ENDCOMP;*/

run;
    ods pdf close;

Output in pdf looks as follows from above proc report (file attached ) :

Have ----> (see the empty space above and below john and for gender and other fields for customer name)

CUST NAME GENDER MARITAL CUST NUM     TRANS DATE

john                 M                m                **********     08/19/2016

CLIENT NAME                                                  CLIENT NUM
john                                                                         1
mary                                                                        3
nirvana                                                                    4
wilson                                                                      2

 Question1) How can I WRAP/make the first row data compact so that no empty space shows up above and below the first row info  like this: so everything looks even and report is pleasing to read and empty rows are deleted /merged together as below

Result Wanted ---->

CUST NAME GENDER MARITAL CUST NUM     TRANS DATE

john                 M                m                **********     08/19/2016

CLIENT NAME                                            CLIENT NUM
john                                                                    1
mary                                                                   3
nirvana                                                                4
wilson                                                                 2

--------------------------------------------------------------End of Information for this customer----------------------------------------------------------------

Question2) How can i insert an end of line marker at end as pointed above in wanted output?

I tried  using below but it messes up report

/*COMPUTE after;*/
/*LINE "________________________________________________________________________________________________________________________________________";*/
/*ENDCOMP;*/

Question3? Any option in sas to make sure that if there is extra information on page then it goes over to next page after the first page is completely filled up. For example in above case lets say 40 clients listed under client name or 40 customer names or both how can i use the entire space on pdf doc of A4 size for all info for a particular customer/primary ID and any remaining information goes on the next /following page

thanks in advance fir any solutions.

[Cynthia_sas · Accepted solution]

Hi:
  I generally don't read XLSX files. If your data are small enough, you might write a DATA step program to read the data as DATALINES.

  However, I do have some other comments on your code:
1) not sure what you are trying to accomplish with WRAP in the TITLE statement. It is not an option
http://support.sas.com/documentation/cdl/en/lestmtsref/69738/HTML/default/viewer.htm#p10gcmrmf83iaxn...
2) LS=256, HEADLINE, etc are LISTING only options that do not apply to ODS PDF. PDF uses the TOPMARGIN, BOTTOMMARGIN, RIGHTMARGIN and LEFTMARGIN options to control the width of text allowed on the page.
3) Why bother with STYLE=LISTING if you are overriding everything with the Calibri font?
4) your code uses formats for gender and married, but no one can run this code because you did not provide the formats
5) I don't know what you mean by "first row data" -- if you did not have such wide widths on your columns, all of the data would fit on one row and would not wrap.
6) why bother specifying rules=none every place when using the simple STYLE=JOURNAL would get rid of all the interior Table lines for you.
7) not sure why you have ALL your report items defined as GROUP
😎 not sure why you have a date format of mmddyy10. for case_number variable when the case_number variable seems to be 12345678 -- which isn't a date.
9) For STYLE(HEADER) on the PROC REPORT statement, it appears that you have conflicting attribute values for some of the border settings. I see BORDERBOTTOMWIDTH specified twice and the values are different.

I am not sure what you mean by "end of line marker" in question #2. ODS LISTING might respect a line of all underlines, but ODS PDF will not produce what you want. The concept of "end of line marker" is typically something you find discussed for data files -- end of record marker and end of file marker. The Journal Style will put a line underneath the table for you at the end of the table automatically.
 
I don't understand your question #3 at all. My recommendation is that you get the other stuff fixed/working, possibly explore the Journal style and then see how close you come to the report you want. Here's an example of what JOURNAL style output would look like for a large number of clients. Simplified code is below.

cynthia

data demo;
  infile datalines dlm=',' dsd;
  input cust_name $ sex $ marital $ case_number lot_num OI_Date : mmddyy.
        client_name $ client_num;
return;
datalines;
john,Male,married,12345678,1234,8/19/2016,john,1
john,Male,married,12345678,1234,8/19/2016,wilson,2
john,Male,married,12345678,1234,8/19/2016,mary,3
john,Male,married,12345678,1234,8/19/2016,nirvana,4
john,Male,married,12345678,5678,8/19/2016,gatsy,5
john,Male,married,12345678,5678,8/19/2016,cassy,6
john,Male,married,12345678,5678,8/19/2016,cassida,7
john,Male,married,12345678,5678,8/19/2016,tim,8
john,Male,married,12345678,5678,8/19/2016,rob,9
john,Male,married,12345678,5678,8/19/2016,alan,10
john,Male,married,12345678,5678,8/19/2016,barb,11
john,Male,married,12345678,5678,8/19/2016,carla,12
john,Male,married,12345678,5678,8/19/2016,dave,13
john,Male,married,12345678,5678,8/19/2016,edward,14
john,Male,married,12345678,5678,8/19/2016,fran,15
john,Male,married,12345678,5678,8/19/2016,gina,16
john,Male,married,12345678,5678,8/19/2016,harry,17
john,Male,married,12345678,5678,8/19/2016,iona,18
john,Male,married,12345678,5678,8/19/2016,jack,19
john,Male,married,12345678,5678,8/19/2016,kathy,20
john,Male,married,12345678,5678,8/19/2016,laura,21
john,Male,married,12345678,5678,8/19/2016,michael,22
john,Male,married,12345678,5678,8/19/2016,nick,23
john,Male,married,12345678,5678,8/19/2016,olivia,24
john,Male,married,12345678,5678,8/19/2016,peter,25
john,Male,married,12345678,5678,8/19/2016,quentin,26
john,Male,married,12345678,5678,8/19/2016,rob,27
john,Male,married,12345678,5678,8/19/2016,sarah,28
john,Male,married,12345678,5678,8/19/2016,tomas,29
john,Male,married,12345678,5678,8/19/2016,ulrich,30
;
run;

ods _all_ close;
title;

proc sort data=demo;
  by lot_num cust_name;
run;
  
ods pdf file='c:\temp\nowidths_journal.pdf' style=journal;
TITLE1 bold f= Calibri h=14pt j=c "REPORT: LOT #byval(lot_num)"             ;
TITLE3 " ";
proc report nofs  data = demo nowd spanrows   split='~'  missing;
    by lot_num ;
columns  cust_name sex marital case_number OI_Date client_name client_num ;
define  cust_name /group page   'CUST NAME'  ;
define  sex     / group  'GENDER'    ;
define  marital     / group 'MARITAL'  ;   
define  case_number     / order 'CUST NUM'  ;
define  OI_Date/ display 'TRANS DATE'  format=mmddyy10.  ;
define  client_name    / display 'CLIENT NAME' ;   
define  client_num / display 'CLIENT NUM'  ;
break after cust_name / PAGE  ;
run;
ods pdf close;