The SAS Output Delivery System and reporting techniques

Dynamic column width in proc report for ODS RTFdestination.

Reply
Contributor
Posts: 50

Dynamic column width in proc report for ODS RTFdestination.

Hello All

I am trying to generate dynamic report with Proc report

I have 15 variables in a dataset, out of which 3 variables are constant for all the subjects and they should be displayed in the report (Subejct, Category, Parameter)

the rest 12 varibles are values collected at various dates, the subject might have maximum 12 dates, but can also only 1 date.

If a subject has only 1 date that means that subject will have only 4 variables to display in the report, where as if a subject has 12 dates that means the subject has 15 variables to display

Ex1: If there are 4 variables for a subject, 3 variables which are constant can be given definitive width (10%, 10%, 10%) . The rest of the width available for that 1 variable should be 69% ( if we assume cellwidth total is 99%)

Ex2  if there are 6 variable for another subject, 3 variables which are constant can be given definitive width (10%, 10%, 10%)  the rest of the width available for 3 more variable should be 23% 23% 23%)


The maximum i can have on one page is onlyTotal 6 (3 constat variable + 3 dated variables)


If there are 9 variables for a subject the other 3 variable should slip into next page (3 constant variables will also appear in the next page as I am using id option in proc report.) Im trying to make the width dynamic but its not allowing me or not getting any idea


ods listing close;

ods rtf file = "&output." style=styles.&rpttype. nogfootnote nogtitle startpage = yes;

%do zz=1 %to &n_rand;  /****** getting each subject**********/

  proc report data=&data nowd missing headskip spacing=1 split='~'   ;

  column page subjid paramn paramcd param dt_1-dt_&&mdt&zz ;   /***** high lighted ones are dated variables which are dynamic***/

  where usubjid eq "&&sid&zz" ;

  define page    /order order=internal noprint;

  define subjid  /order order=internal noprint;

  define paramn  /order order=internal noprint;

  define paramcd /order order=internal noprint;

  define param   /order order=internal style={just=left asis=on cellwidth=30%} id flow left  "Laboratory Test";

  %do xx=1 %to &&mdt&zz;   /****** dated variables *******/

  define dt_&xx /display "&&v&zz.&xx"  %if &xx=5 or &xx=9 or &xx=13 %then page;

                                       %if &&mdt&zz>4 and &xx<=4 %then %do; style={just=left asis=on cellwidth=%eval(68/4)%} ; %end;

  %else  %if 5<=&&mdt&zz<=8 and 4<&xx<=8 %then %do; style={just=left asis=on cellwidth=%eval(68/(&&mdt&zz-4))%} ; %end;

                                %else  %if 9<=&&mdt&zz<=12 and 4<&xx<=8 %then %do; style={just=left asis=on cellwidth=%eval(68/4)%} ; %end;

  %else  %if 9<=&&mdt&zz<=12 and 8<&xx<=12 %then %do; style={just=left asis=on cellwidth=%eval(68/(&&mdt&zz-8))%} ; %end;

  ;

  %end;

run;

%end;

ods rtf close;

ods listing;


Capture.PNG

SAS Super FREQ
Posts: 8,815

Re: Dynamic column width in proc report for ODS RTFdestination.

Hi:

  I don't see your %macro/%mend statements for the macro program definition. You can't have %IF or %DO in open code. Are you getting error messages?

  This paper had some suggestions about using ID and PAGE: http://support.sas.com/resources/papers/proceedings14/SAS038-2014.pdf see page 15/16.

  This paper has some information about %IF and %DO: http://support.sas.com/resources/papers/proceedings13/120-2013.pdf see page 17/18

cynthia

Contributor
Posts: 50

Re: Dynamic column width in proc report for ODS RTFdestination.

Cynthia

I have %macro and %mend already in the program, just I didnt include it here!. I am not getting any error, but the alignment/arrangement is not happening according to my wanted output!!

I was looking for some ideas

Contributor
Posts: 50

Re: Dynamic column width in proc report for ODS RTFdestination.

the problem is my code actually works but somehow SAS is automatically taking cellwidth =13% if my page is rolled over to second page for some subjects!

Super User
Posts: 11,105

Re: Dynamic column width in proc report for ODS RTFdestination.

You may be having issues with using %eval unless you want decimals truncated as %eval only does integer arithmetic. You might want to use %sysevalf if &&mdt&zz has values near 9

Contributor
Posts: 50

Re: Dynamic column width in proc report for ODS RTFdestination.

Somehow the cellwidth is being take only 13.6 even after usin sysevalf, it suppose to take 68/4 which is 17

Super User
Posts: 11,105

Re: Dynamic column width in proc report for ODS RTFdestination.

You might post your updated code using %sysevalf.

And since you are only showing part of the macro (obvious since you have %do /%end construct which won't work in open code) you may be having issues elsewhere as well. Such as none of your comparisons resolving to true and getting a default.

Super User
Super User
Posts: 7,682

Re: Dynamic column width in proc report for ODS RTFdestination.

Another alternative is to pre compute this in the dataset.  So:

data have;

  subj="001"; cat="abc"; param="def"; d1="01jan14"d; d2="14jan14"d; d3="15jan14"d; d4="18jan14"d; output;

  subj="002"; cat="abc"; param="def"; d1="01jan14"d; output;

  subj="003"; cat="abc"; param="def"; d1="01jan14"d; d2="14jan14"d; d3="15jan14"d; output;

run;


ods rtf ...;

data _null_;

  set have;

  /* Page 1 */

  call execute('proc report data=have ...; columns subj cat param ');

  if d1 ne. then call execute('d1');

  if d2 ne. then call execute('d2');

  if d3 ne. then call execute('d3');

  call execute('; define subj / "subj" order; define cat / "cat" order; define param / "Param" order; ');

  if d2 ne . and d3 ne . then width=5;

  else if d2 ne . then width=8;

  else width=10;

  if d1 ne. then call execute('define d1 / "Adate" style(column)=[cellwidth='||put(width,2.)||'];');

  if d2 ne. then call execute('define d2 / "Adate2" style(column)=[cellwidth='||put(width,2.)||'];');

  if d3 ne. then call execute('define d3 / "Adate3" style(column)=[cellwidth='||put(width,2.)||'];');

  call execute('run;');

  /* Page 2 */

  if d4 ne . then do;

    call execute('proc report data=have ...; columns subj cat param d4;');

    call execute('define subj / "subj" order; define cat / "cat" order; define param / "Param" order; define d4 / "Adate4"; run;');

    /* Note just assumed its there so no generic */

  end;

run;

Ask a Question
Discussion stats
  • 7 replies
  • 926 views
  • 0 likes
  • 4 in conversation