Spacing issue in ODS output

[Q1983]

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 ?

[Reeza]

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

[ballardw · Accepted solution]

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.

[Q1983]

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

[Reeza]

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;

[Cynthia_sas]

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