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 .
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.