## DATA Step, Macro, Functions and more

Solved
Occasional Contributor
Posts: 15

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

Accepted Solutions
Solution
‎09-05-2016 05:45 AM
Super User
Posts: 10,766

```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;

```

All Replies
Super User
Posts: 9,599

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.

Super User
Posts: 23,667

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

Occasional Contributor
Posts: 15

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;
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; ``````
Super User
Posts: 23,667

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.

Solution
‎09-05-2016 05:45 AM
Super User
Posts: 10,766

```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;

```
☑ This topic is solved.