BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Milouda
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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

Milouda
Obsidian | Level 7

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
Super User

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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 5 replies
  • 1648 views
  • 1 like
  • 4 in conversation