topic Re: ACROSS option in PROC REPORT in SAS Enterprise Guide

ACROSS option in PROC REPORT

Daily1 — Mon, 12 Feb 2024 09:40:50 GMT

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

Re: ACROSS option in PROC REPORT

PaigeMiller — Mon, 12 Feb 2024 10:40:28 GMT

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).

Re: ACROSS option in PROC REPORT

Ksharp — Mon, 12 Feb 2024 11:34:52 GMT

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;

Re: ACROSS option in PROC REPORT

Daily1 — Mon, 12 Feb 2024 12:00:06 GMT

pls use
ods tagsets.excelxp file="STATE.XLS"
style=mystyle options(scale="70" print_footer_margin="0.3" Center_Horizontal="Yes" sheet_name="report" papersize="A4" skip_space='0,0,0,0,0' orientation='landscape'
suppress_bylines='yes'
Autofit_height='yes'
embedded_footnotes= 'YES'
embedded_titles= 'Yes' print_footer='&P'

);

Re: ACROSS option in PROC REPORT

PaigeMiller — Mon, 12 Feb 2024 12:01:19 GMT

We need to see your exact PROC REPORT code that didn't work.

Re: ACROSS option in PROC REPORT

Daily1 — Mon, 12 Feb 2024 12:07:36 GMT

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='&amp;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

Re: ACROSS option in PROC REPORT

PaigeMiller — Mon, 12 Feb 2024 12:26:03 GMT

Instead of split='/' try it with split='~'

Re: ACROSS option in PROC REPORT

Daily1 — Mon, 12 Feb 2024 12:59:53 GMT

l'm trying as you say but still no worked .
Same output Show

Re: ACROSS option in PROC REPORT

PaigeMiller — Mon, 12 Feb 2024 13:30:12 GMT

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;

Re: ACROSS option in PROC REPORT

Daily1 — Mon, 12 Feb 2024 13:40:00 GMT

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='&amp;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

Re: ACROSS option in PROC REPORT

PaigeMiller — Mon, 12 Feb 2024 13:45:21 GMT

It works with ODS EXCEL.

I don't use ODS TAGSETS.EXCELXP so I cannot determine why it doesn't work properly.

Re: ACROSS option in PROC REPORT

Kurt_Bremser — Mon, 12 Feb 2024 14:41:07 GMT

@Daily1 wrote
pls 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.

Re: ACROSS option in PROC REPORT

Ksharp — Tue, 13 Feb 2024 03:01:28 GMT

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='&amp;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;