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 ?
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.
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
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.
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
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;
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.