BookmarkSubscribeRSS Feed
Daily1
Quartz | Level 8

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

Daily1_0-1707730604550.png

i want 

Daily1_1-1707730644504.png

 

 

 

12 REPLIES 12
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User

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;

Ksharp_0-1707737686714.png

 

Daily1
Quartz | Level 8
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'

);
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Daily1
Quartz | Level 8

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 

Daily1_0-1707739376145.png

i want 

Daily1_1-1707739501111.png

 

 

 

 

PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller
Daily1
Quartz | Level 8
l'm trying as you say but still no worked .
Same output Show
PaigeMiller
Diamond | Level 26

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;

 

 

 

--
Paige Miller
Daily1
Quartz | Level 8
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 

Daily1_0-1707744960039.png

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

 

pls use 

ods tagsets.excelxp

 

PaigeMiller
Diamond | Level 26

It works with ODS EXCEL.

 

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

--
Paige Miller
Kurt_Bremser
Super User

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

Ksharp
Super User

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;

Ksharp_0-1707793279871.png

 

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 358 views
  • 0 likes
  • 4 in conversation