topic Re: several question for ods excel using proc report with line statement in SAS Programming

several question for ods excel using proc report with line statement

JeffSAS — Tue, 19 Jul 2022 22:41:39 GMT

proc sql noprint;select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 into :s1,:s2,:s3,:ratio from sashelp.class; quit;
%put s1=&s1. s2=&s2. s3=&s3. ratio=&ratio.;

ods excel close;
 ods excel file="c:///class1.xlsx" options(sheet_name="tab1" autofilter='1-2');
    ods escapechar='^';
   ods text=" total age is  |     total hight is      |  total weight is   | % ratio ";
   ods text=  "   &s1.  |         &s2.    |     &s3.   |   &ratio. ";

proc report data=sashelp.class  nocenter;
  column name sex age height weight;
  define name -- weight/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
  define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total';
  compute before;
    call define(_row_,'style', "style=[tagattr='formula:=subtotal(9,indirect(concatenate(address(2,column()),"":"",address(row()-1,column()))))']"); *for all numeric columns sum;
  endcomp;
run;
title;
ods excel close;

I want to generate an.excel file with text in the report using ods excel/proc report line statement, but there are several questions:

1. can each header align to the left side of each cell?

2. the '%' is omitted in the .xlsx report (i.e. % ratio in ods text statement, they should show up in row1 and row 2 in the report), how I can keep it in the report?

3. the total (in line 4 in the report) for each column are 0s, and how can they display the correct numbers(i.e. the total age should 253 instead of 0).

4. is there any way to control the ods test length or format, because if the test is longer, the test will be wrapped in multiple rows(i.e. row 1 and row 2, I need it stay in one row)

thanks!

Re: several question for ods excel using proc report with line statement

andreas_lds — Wed, 20 Jul 2022 04:46:13 GMT

You could do this without macro-variables, if an additional column is acceptable:

proc report data=sashelp.class nocenter;
   column name sex age height weight Ratio;

   define age / analysis sum format=comma12. style(column)=[cellwidth=.5in];
   define height / analysis sum format=comma12. style(column)=[cellwidth=.8in];
   define weight / analysis sum format=comma12. style(column)=[cellwidth=.8in];
   define Ratio / computed format=percent8.2;
   
   compute ratio;
      ratio = height.sum / weight.sum;
   endcomp;

   rbreak before / summarize;
run;

Re: several question for ods excel using proc report with line statement

Ksharp — Wed, 20 Jul 2022 12:23:36 GMT

I don't understand your fourth requirement .

proc sql noprint;select sum(age),sum(height),sum(weight), sum(height)/sum(weight) format=percent8.2 
 into :s1,:s2,:s3,:ratio trimmed from sashelp.class; quit;
%put s1=&s1. s2=&s2. s3=&s3. ratio=&ratio.;

ods excel close;
 ods excel file="c:\temp\class1.xlsx" options(sheet_name="tab1" autofilter='1-2' flow='table');
    ods escapechar='^';
  ods text=" total age is   |     total hight is    |  total weight is  | %% ratio  ";
  ods text= "^_^_&s1.       |         &s2.      |     &s3.       |   &ratio.% ";
proc report data=sashelp.class nowd nocenter style(header)={just=l};
  column name sex age height weight;
  define name -- weight/ display;
  define age  / analysis sum format=comma12. style(column)= [cellwidth=.5in];
  define height / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
  define weight / analysis sum format=comma12. style(column)= [cellwidth=.8in];*newly added column;
rbreak before / summarize style=[background=lightblue font_weight=bold];* CONTENTS='Total';
run;
title;
ods excel close;

Re: several question for ods excel using proc report with line statement

JeffSAS — Wed, 20 Jul 2022 12:23:45 GMT

thanks!

I need the rows 1-2 information as well.

and the '% ratio' was changed to 'ratio' and the '62.32%' was changed to '62.32', and wonder if we can fix that.

also, can we control the row 1-2 layout, like start with spaces in both rows?

can we adjust headers name & sex to align to the left?

I tried to use line statement, but it caused the wrapping issue.

Re: several question for ods excel using proc report with line statement

ballardw — Wed, 20 Jul 2022 14:28:04 GMT

I strongly suggest that if you are going to place things like ratio calculation results into macro variables that you take the time to control the conversion by creating a string with known properties, i.e. Put with a format. The SAS routines for converting numbers to text that would be used otherwise can lead to some oddities when a value is rounded differently than you may expect for creating the macro variable(s). You can spend a LOT of time trying to run down odd boundary value issues where you get some unexpected results intermittently. Go ahead, ask how I know. ☹️

Re: several question for ods excel using proc report with line statement

JeffSAS — Wed, 20 Jul 2022 16:49:34 GMT

you are right, and I did spent lots of time on aligning them.

could you please share your solution, thanks!

Re: several question for ods excel using proc report with line statement

ballardw — Wed, 20 Jul 2022 19:54:36 GMT

@JeffSAS wrote:

you are right, and I did spent lots of time on aligning them.

could you please share your solution, thanks!

First thing is to remember that macro variables are always text. So if create a text value an place that in to the macro variable then you get what you want if the purpose is display. This would have the % as part of the text value of the macro variable for the ratio.

proc sql noprint;
   select sum(age),sum(height),sum(weight),  
   put (sum(height)/sum(weight),percent8.2) into :s1,:s2,:s3,:ratio 
   from sashelp.class; 
quit;

If you might need the macro variable for calculation you might consider rounding the value

proc sql noprint;
   select sum(age),sum(height),sum(weight), 
   round(sum(height)/sum(weight), 0.0001)  into :s1,:s2,:s3,:ratio 
   from sashelp.class; 
quit;

And apply the percent or other formats after calculations.

I would consider one of these approaches for any macro variable created that would contain decimal portions.