BookmarkSubscribeRSS Feed
SASSLICK001
Obsidian | Level 7

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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

SASSLICK001
Obsidian | Level 7

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

SASSLICK001
Obsidian | Level 7

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!

ballardw
Super User

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

SASSLICK001
Obsidian | Level 7

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

ballardw
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 2040 views
  • 0 likes
  • 4 in conversation