The SAS Output Delivery System and reporting techniques

Spacing issue in ODS output

Accepted Solution Solved
Reply
Regular Contributor
Posts: 180
Accepted Solution

Spacing issue in ODS output

 

ODS TAGSETS.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&dors_style options( sheet_interval='bygroup' );

 

 

/*%CoverSheet(&ReportName,&pbd_date_key,&Version);*/

proc sort data=al_loans out=al_loans_out;

by prop_state;

run;

proc print data=al_loans_out label noobs width=minimum ;

by prop_state;

label lnkey ='LOAN_NUM' BORR_LAST='BORROWER_LAST_NAME' app_date_f ='APP_DATE'

STAT_AGGR_DT_f ='CLOSED_DT'

LTV ='LTV' LOAN_TYPE ='LOAN_TYPE' purp ='LN_PURPOSE' occupancy ='OCCUPANCY' CHNL_CD ='CHANNEL'

BRANCHID ='BRANCHID' STTL_PCK_RCV_DT_f ='SETTLEMENT_PKG_RECEIVED_DT' PROP_ADDR ='PROP_ADDR'

prop_addr2 ='PROP_ADDR2' PROP_CITY ='PROP_CITY' prop_state ='PROP_STATE' PROP_ZIP ='PROP_ZIP'

BORR_ADDR = 'CURRENT_ADDR' prop_county ='PROP_COUNTY' BORR_ADDR2 ='BORR_ADDR2' BORR_CITY ='CURRENT_CITY'

BORR_STATE ='CURRENT_prop_state' BORR_ZIP ='CURRENT_ZIP' MAILINGSTREET ='Mailing_Address' MAILINGSTREET2 ='Mailing_Address2'

MAILINGCSZ ='MAILING CITY, prop_state, ZIP' CLOSER_NAME ='CLOSER_NAME' CLOSER_EMAIL ='CLOSER_EMAIL' U_CLOSERPHONE

RESPA_REFUND_AMOUNT ='RESPA_REFUND_AMOUNT' discl_type ='POST_CLOSING_DISCLOSURE_TYPE' U_RES_REV_ST_f ='RESPA_REVIEW_STARTED'

U_RES_REV_em_f ='RESPA_REVIEW_COMPLETED' File_Number ='HUD_FILE_NUMBER'

SETTLEMENT_AGENT_COMPANY = 'SETTLEMENT_AGENT_COMPANY' SETTLEMENT_AGENT_EMAIL = 'SETTLEMENT_AGENT_EMAIL'

SETTLEMENT_AGENT_PHONE = 'SETTLEMENT_AGENT_PHONE';

run;

ods listing close;

options missing=' ' mlogic mprint;

TITLE; FOOTNOTE;

 

ODS TAGSETS.ExcelXP close;

ods listing;

quit;

The columns for this output are stretched and I want them to extend just to the length of the label statement only.  I used width = minimum however they still stretch within the output tabs.  Is there another way to control the column widths that I am missing?  Is It that the column names are in caps or too long themselves ?

 

 

 

 

 

 


Accepted Solutions
Solution
‎01-17-2018 07:39 PM
Super User
Posts: 13,909

Re: Spacing issue in ODS output

Likely you'll need to add something similar to this to your proc print code.

 

var STAT_AGGR_DT_f /style=[cellwidth=1.2in];

for each variable. You can use different units such as CM or PT instead of IN if you prefer.

 

However the output being a spreadsheet if you have other output before the proc print any "column" prior to the print you might not get the width you want.

View solution in original post


All Replies
Super User
Posts: 23,980

Re: Spacing issue in ODS output

You need to use the column width options. I don't see any ODS options used besides bygroup, but thats the way to control the ODS output.

 

List of options is here:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Solution
‎01-17-2018 07:39 PM
Super User
Posts: 13,909

Re: Spacing issue in ODS output

Likely you'll need to add something similar to this to your proc print code.

 

var STAT_AGGR_DT_f /style=[cellwidth=1.2in];

for each variable. You can use different units such as CM or PT instead of IN if you prefer.

 

However the output being a spreadsheet if you have other output before the proc print any "column" prior to the print you might not get the width you want.

Regular Contributor
Posts: 180

Re: Spacing issue in ODS output

That worked.  One more question.  When I use the code

proc print data=al_loans_out label noobs ;

by prop_state;

var lnkey /style=[cellwidth=1.0in];

var BORR_LAST /style=[cellwidth=2.0in];

label lnkey ='LOAN_NUM' BORR_LAST='BORROWER_LAST_NAME';

run;

 

Since I am doing a group by prop_state, it segments each in its own tab based on state. At the top I get a header prop_state="the state name".  Is there a way I can eliminate that header and still have the name of the state only appear in the tab?

instead of prop_state = AL just show AL

Super User
Posts: 23,980

Re: Spacing issue in ODS output

You can control that with the Sheet_Label or Sheet_Name options within the Tagset Options. 

 

I think the 'header' you're referring to is the BYLINE and you can set that with an option statement.

 

options nobyline;

 

 

SAS Super FREQ
Posts: 9,424

Re: Spacing issue in ODS output

Yes, there is a way. Look at the code example and screen shots in this posting https://communities.sas.com/t5/ODS-and-Base-Reporting/Creating-Different-Worksheet-for-Each-Group-By... -- even though you are using PROC PRINT, if you have the suboptions set correctly, you should be able to generate the same type of output for your BY groups with PROC PRINT.

cynthia
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 260 views
  • 4 likes
  • 4 in conversation