About proc report

[Milouda]

I used macro to retrieve datasets of statistics of a set of variables.

Now that I want to print a report, I am wondering if there is some trick to minimise the lines of the code without having to repeat proc report statement. here is my whole code

proc format;
   value grp   1='Group 1' 2='Group 2' 3='Total'; 
   value $sex 'F'='Female' 'M'='Male';
   value origin  1 ='Belgium' 2='other';  
   run; 
 /*get data and create missing variables*/
   data Class1;
   set sashelp.class;
   group  = rantbl(12345,.5); 
   origin = rantbl(12345,.5);
   _height=height*2.54;
   _Weight=weight/2.2;
   bmi  = (weight*703) / height**2;
   attrib age     format=F3.0    label='Age (years)';
   attrib _height  format=F7.1    label='Height (cm)';
   attrib _weight  format=F7.1    label='Weight (kg)';
   attrib sex     format=$sex.   label='Gender';
   attrib origin   format=origin.   label='Country of birth';
   attrib bmi     format=F7.2    label='BMI (kg/m**2)';
   attrib group     format=grp.    label='Group';
   run; 
   /* create total variable*/
data Class1Tot;
   set Class1;
   attrib ctrt length=$1 label='Character grp'; 
   do group= group, 3;
      ctrt = substr('ABC',group,1); 
      output;
      end; 
   run; 


%Macro tablestat(dataset=,Var1=,char=);
        
proc summary data=&dataset completetypes;
    Class group/preloadfmt;
	var &Var1;
 output out=stat median=med mean=mn std=StD q1=q1 q3=q3 n=n max=max min=min;
 run;
 data stat;
 set stat;
 if group=. then delete;
 mnx=compress(min)!!' - '!! compress(max);
 q12=compress(q1)!!' - '!! compress(q3);
 mnsd= compress(put(mn,8.1))!!' ('!!compress(put(std,8.1))!!')';
 nx=put(_freq_,8.0);
 medx=put(med,8.0);
 drop q1 q3 mn std _freq_ med n max min _type_;
 run;

 data t1;
 set stat;
 array b(*) $ nx mnsd medx q12 mnx;
 do i=1 to 5;
 c=b(i);
 output;
 end;
 drop nx mnsd medx q12 mnx ;
run; 
proc sql;
 create table C1 as
    select a.i,a.c,b.c as c2 
	from T1(where =(group EQ 1)) as a left join T1(where =(group EQ 2)) as b
	on a.i=b.i;

	create table &var1 as
	select d.*,e.c as c3
	 from c1 as d left join	 T1(where =(group EQ 3)) as e
	 on d.i=e.i;
 quit;
 data &var1;
 set &var1;
 length char $200.;
 char="&char";
 run;
 %mend;
 %tablestat(dataset=Class1Tot,var1=_height,char=Height (cm));

  %macro category(data=,var=,char=); 
 ods listing close;
 proc freq data=&data;
 tables &var*group/norow nopercent;
 ods output CrossTabFreqs=discret(where=(_type_ in('11')));
 run;
 ods listing;
 data T1;
  set discret;
  nx=compress(put(frequency,8.0))!!'('!!compress(put(Colpercent,8.1))!!'%)';
  drop _type_ table _table_ Missing Frequency Colpercent;
 run;
 
 proc sql;
  create table T2 as
   select a.&var,a.nx, b.nx as c1 
   from T1(where=(group EQ 1)) as a left join T1(where=(group EQ 2)) as b
        on a.&var=b.&var;
  create table &var as
    select d.*,e.nx as c2
	 from T2 as d left join T1(where=(group EQ 3)) as e
	 on d.&var=e.&var;
   quit;
data &var;
 set &var(rename=(nx=c));
 length char $200.;
 char="&char";
 run;
 %mend;
 %category(data=Class1tot,var=origin,char=country of birth);
 /*-----Display---*/

Thanks a lot

[RW9]

Perhaps it would be easier to post some example test data (in the form of a datastep) and what the output should look like.  A simple proc means with a by group, then a datastep to process the output should be sufficient.

[Reeza]

Am I missing something, there's no proc report in your code?

[Milouda]

I am sorry this is what I did , in an attempt to display my stats as in the template that you will find in the attachment

proc format;
   invalue statgrp(upcase just) 'N'=1 'MEAN','STD'=2 'MEDIAN'=3 'Q1','Q3'=4  'MIN','MAX'=5;
   value statgrp 1='N' 2='Mean (SD)' 3='Median' 4='Q1 - Q2' 5='Min - Max';
 run;
  %tablestat(dataset=Class1Tot,var1=age,char= Age (years));
  %category(data=Class1tot,var=sex,char=Gender);
  %category(data=Class1tot,var=origin,char=country of birth);
  %tablestat(dataset=Class1Tot,var1=_height,char= Height (cm));
  %tablestat(dataset=Class1Tot,var1=_weight,char= Body weight (kg));
  %tablestat(dataset=Class1Tot,var1=bmi,char= Body Mass Index);

	  data final;
          set age sex origin _height _weight bmi; 
        run;

 /*-----Display---*/

 ods listing close;
ods rtf file="C:\Users\mbouichou\Downloads\mydata\macro_print.rtf" ; 
title4 j=center h=14pt 'Table :age statistics ';

proc report data=final spanrows nowd;
  Column  char STAT c c2 c3;
  define char/group noprint;
  define STAT/group ;
  define c/display "Group 1";
  define c2/display "Group 2";
  define c3/display "Total";
  run;
  ods rtf close;
ods listing; 

[Reeza]

I'm still not sure where proc report would be repeated and what your question is, besides maybe how to do this efficiently?

if so, take a look at papers by either Cynthia Zender (complex reports) or John King

Example 8

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

Note the code is available on the SAS website as well. 

In in general if you search lexjansen.com for clinical reports you'll find many papers and code samples. 

[Ksharp · Accepted solution]

Check my SQL here:
https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-for-pdf-report/m-p/295392#M16749

Especially check dataset TEMP , and know what is my intention .




Here is simple version of it :

data demog;
label subjid = "Subject Number"
trt = "Treatment"
gender = "Gender"
race = "Race"
age = "Age";
input subjid trt gender race age @@;
datalines;
101 0 1 3 37 301 0 1 1 70 501 0 1 2 33 601 0 1 1 50 701 1 1 1 60
102 1 2 1 65 302 0 1 2 55 502 1 2 1 44 602 0 2 2 30 702 0 1 1 28
103 1 1 2 32 303 1 1 1 65 503 1 1 1 64 603 1 2 1 33 703 1 1 2 44
104 0 2 1 23 304 0 1 1 45 504 0 1 3 56 604 0 1 1 65 704 0 2 1 66
105 1 1 3 44 305 1 1 1 36 505 1 1 2 73 605 1 2 1 57 705 1 1 2 46
106 0 2 1 49 306 0 1 2 46 506 0 1 1 46 606 0 1 2 56 706 1 1 1 75
201 1 1 3 35 401 1 2 1 44 507 1 1 2 44 607 1 1 1 67 707 1 1 1 46
202 0 2 1 50 402 0 2 2 77 508 0 2 1 53 608 0 2 2 46 708 0 2 1 55
203 1 1 2 49 403 1 1 1 45 509 0 1 1 45 609 1 2 1 72 709 0 2 2 57
204 0 2 1 60 404 1 1 1 59 510 0 1 3 65 610 0 1 1 29 710 0 1 1 63
205 1 1 3 39 405 0 2 1 49 511 1 2 2 43 611 1 2 1 65 711 1 1 2 61
206 1 2 1 67 406 1 1 2 33 512 1 1 1 39 612 1 1 2 46 712 0 . 1 49
;
run;

%macro test1(group=,group1=,group2=,pvalue=);
select &group as group,
       "&group1" as group1 length=20,
       "&group2" as group2 length=20,
	   "&pvalue" as value length=20
 from demog(obs=1)
%mend;

%macro test2(group=,group1=,group2=,var=,trt=);
select &group as group,
       "&group1" as group1 length=20,
       "&group2" as group2 length=20
%if %upcase(&group2) ne PVALUE %then %do;
 %if %upcase(&group1)=N %then %do;      
       ,put(count(&var),8.-l) as value length=20
        from demog
 %end;
 %else %if %upcase(&group1)=MEAN %then %do;      
       ,put(mean(&var),8.1-l) as value length=20
        from demog
 %end;
 %else %if %scan(%upcase(&group1),1)=STANDARD %then %do;      
       ,put(std(&var),8.2-l) as value length=20
        from demog
 %end;
 %else %if %upcase(&group1)=MINIMUM %then %do;      
       ,put(min(&var),8.1-l) as value length=20
        from demog
 %end;
 %else %if %upcase(&group1)=MAXIMUM %then %do;      
       ,put(max(&var),8.1-l) as value length=20
        from demog
 %end;
%end;

%else %do;
  	   ,' ' as value length=20
       from demog(obs=1)
%end;
  %if %length(&trt) %then %do; where trt=&trt  %end;
%mend;


proc sql;
create table temp as

%test1(group=1,group1=Age(years),group2=Active,pvalue=)
union all
%test1(group=1,group1=Age(years),group2=Placebo,pvalue=)
union all
%test1(group=1,group1=Age(years),group2=Overall,pvalue=)
union all
%test1(group=1,group1=Age(years),group2=Pvalue,pvalue=0.95)
union all


%test2(group=1,group1=N,group2=Active,var=age,trt=1) 
union all
%test2(group=1,group1=N,group2=Placebo,var=age,trt=0) 
union all
%test2(group=1,group1=N,group2=Overall,var=age,trt=) 
union all
%test2(group=1,group1=N,group2=Pvalue,var=,trt=) 
union all


%test2(group=1,group1=Mean,group2=Active,var=age,trt=1) 
union all
%test2(group=1,group1=Mean,group2=Placebo,var=age,trt=0) 
union all
%test2(group=1,group1=Mean,group2=Overall,var=age,trt=) 
union all
%test2(group=1,group1=Mean,group2=Pvalue,var=,trt=) 
union all



%test2(group=1,group1=Standard Deviation,group2=Active,var=age,trt=1) 
union all
%test2(group=1,group1=Standard Deviation,group2=Placebo,var=age,trt=0) 
union all
%test2(group=1,group1=Standard Deviation,group2=Overall,var=age,trt=) 
union all
%test2(group=1,group1=Standard Deviation,group2=Pvalue,var=,trt=) 
union all



%test2(group=1,group1=Minimum,group2=Active,var=age,trt=1) 
union all
%test2(group=1,group1=Minimum,group2=Placebo,var=age,trt=0) 
union all
%test2(group=1,group1=Minimum,group2=Overall,var=age,trt=) 
union all
%test2(group=1,group1=Minimum,group2=Pvalue,var=,trt=) 
union all



%test2(group=1,group1=Maximum,group2=Active,var=age,trt=1) 
union all
%test2(group=1,group1=Maximum,group2=Placebo,var=age,trt=0) 
union all
%test2(group=1,group1=Maximum,group2=Overall,var=age,trt=) 
union all
%test2(group=1,group1=Maximum,group2=Pvalue,var=,trt=) 
union all
/****************************/


%test1(group=2,group1=Gender,group2=Active,pvalue=)
union all
%test1(group=2,group1=Gender,group2=Placebo,pvalue=)
union all
%test1(group=2,group1=Gender,group2=Overall,pvalue=)
union all
%test1(group=2,group1=Gender,group2=Pvalue,pvalue=0.265) 

;
quit;



proc transpose data=temp out=want(drop=_name_);
 by group group1 notsorted;
 var value;
 id group2;
run;

title "Company";
footnote1 "Created by %sysget(SAS_EXECFILENAME) on  &sysdate9.";
footnote2 "Created by &_sasprogramfile on &sysdate9.";
proc report data=want nowd;
define group/group noprint;
define group1/ ' ';
compute before group;
 line ' ';
endcomp;
run;