Hi, I have a question regards to ACROSS option in PROC REPORT. In Scheme name column, hide split data.
PFA
proc report data= data split='/'
;
column DEPT_CODE DEPT_NAME (SCHEME_NAME, (GENERAL SCSP TSP));
define DEPT_CODE / 'Long/label/for/Name' style={font_face='Calibri' font_size=10pt cellwidth=104px};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt cellwidth=1040px cellheight=100px vjust=m just=c};
define DEPT_NAME / display style(column)={just=c cellwidth=1in};
run;
Output is
i want
Try using a different split character like '~'
You really should show us the actual code that is causing the problems, and not something that is not the real code like this:
define DEPT_CODE / 'Long/label/for/Name'
PS: Most of us will not (or cannot) download and open Excel files as they are a security threat. Data should be provided as text, in the form of working SAS data step code (examples and instructions).
I think you should post your real code and data, especially the part of how to generate the excel file .
My code have not such problem at all.
data have;
set sashelp.class;
SCHEME_NAME='Supply of Medicines to various /
Veterinary Dispensaries /
Grant in aid for Mechanised
Vessels, Contribution for Deep Sea /
Fishing Crafts';
run;
ods excel file='c:\temp\want.xlsx' ;
proc report data= have split='/' ;
column name sex (SCHEME_NAME, (weight height));
define name / 'Long/label/for/Name' style={font_face='Calibri' font_size=10pt};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt vjust=m just=c};
define sex / display style(column)={just=c cellwidth=1in};
run;
ods excel close;
We need to see your exact PROC REPORT code that didn't work.
sample code is
data have;
set sashelp.class;
SCHEME_NAME='Supply of Medicines to various /
Veterinary Dispensaries /
Grant in aid for Mechanised
Vessels, Contribution for Deep Sea /
Fishing Crafts';
run;
ods tagsets.excelxp file="Cars.XLS"
options(scale="78" print_footer_margin="0.5" Center_Horizontal="Yes" papersize="A4" skip_space='0,0,0,0,0' sheet_name="report" orientation='landscape'
suppress_bylines='yes'
Autofit_height='yes'
embedded_footnotes= 'YES'
embedded_titles= 'Yes' print_footer='&P'
);proc report data= have split='/' ;
column name sex (SCHEME_NAME, (weight height));
define name / 'Long/label/for/Name' style={font_face='Calibri' font_size=10pt};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt vjust=m just=c};
define sex / display style(column)={just=c cellwidth=1in};
run;
ods excel close;
output is
i want
Instead of split='/' try it with split='~'
Never tell us it doesn't work without showing us the code you used. And if there are still errors in the log, we also need to see the log.
Works for me with ODS EXCEL
data have;
set sashelp.class;
SCHEME_NAME='Supply of Medicines to various ~
Veterinary Dispensaries ~
Grant in aid for Mechanised
Vessels, Contribution for Deep Sea~
Fishing Crafts';
run;
ods excel file="Cars.XLSX"
options(scale="78" print_footer_margin="0.5" Center_Horizontal="Yes" papersize="A4" skip_space='0,0,0,0,0' sheet_name="report" orientation='landscape'
suppress_bylines='yes'
Autofit_height='yes'
embedded_footnotes= 'YES'
embedded_titles= 'Yes'
);
proc report data= have split='~' ;
column name sex (SCHEME_NAME, (weight height));
define name / 'Long~label~for~Name' style={font_face='Calibri' font_size=10pt};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt vjust=m just=c};
define sex / display style(column)={just=c cellwidth=1in};
run;
ods excel close;
data have;
set sashelp.class;
SCHEME_NAME='Supply of Medicines to various ~
Veterinary Dispensaries ~
Grant in aid for Mechanised
Vessels, Contribution for Deep Sea ~
Fishing Crafts';
run;
ods tagsets.excelxp file="Cars.XLS"
options(scale="78" print_footer_margin="0.5" Center_Horizontal="Yes" papersize="A4" skip_space='0,0,0,0,0' sheet_name="report" orientation='landscape'
suppress_bylines='yes'
Autofit_height='yes'
embedded_footnotes= 'YES'
embedded_titles= 'Yes' print_footer='&P'
);
proc report data= have split='~' ;
column name sex (SCHEME_NAME, (weight height));
define name / 'Long/label/for/Name' style={font_face='Calibri' font_size=10pt};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt vjust=m just=c};
define sex / display style(column)={just=c cellwidth=1in};
run;
ods excel close;
output
l'm trying as you say but still no worked .
Same output Show
pls use
ods tagsets.excelxp
It works with ODS EXCEL.
I don't use ODS TAGSETS.EXCELXP so I cannot determine why it doesn't work properly.
@Daily1 wrotepls use
ods tagsets.excelxp
Why, in $DEITY's name? TAGSETS.EXCELXP (which creates XML loadable by Excel) is a stopgap designed before the much better ODS EXCEL (using the XLSX engine) became available.
Try this one :
data have;
set sashelp.class;
SCHEME_NAME='Supply of Medicines to various /
Veterinary Dispensaries /
Grant in aid for Mechanised
Vessels, Contribution for Deep Sea /
Fishing Crafts';
run;
title;
ods tagsets.excelxp file="c:\temp\Cars.XLS"
options(scale="78" print_footer_margin="0.5" Center_Horizontal="Yes" papersize="A4" skip_space='0,0,0,0,0' sheet_name="report" orientation='landscape'
suppress_bylines='yes'
/*Autofit_height='yes' <-- get rid of this line*/
embedded_footnotes= 'YES'
embedded_titles= 'Yes' print_footer='&P'
);proc report data= have split='/' style(header)={cellwidth=200}; /*<----*/
column name sex SCHEME_NAME,(weight height);
define name / 'Long/label/for/Name' style={font_face='Calibri' font_size=10pt};
define SCHEME_NAME / across '' style(column)={font_face='Calibri' font_size=10pt vjust=m just=c };
define sex / display style(column)={just=c };
run;
ods tagsets.excelxp 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 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: