topic auto report in ODS and Base Reporting

auto report

Ying — Thu, 16 Apr 2015 21:55:35 GMT

Hello,

I would like to have a report like below on Excel but with minimun manually work. All I could think about is to have a template with below format and each cell point to the sheet from program Excel output. Is there way directly to have this report instead including format? Please share some your ideas. Thank you!

	Overall	City 1	City 2
Customer Profile
Average Age	45	36	42

Gender
Femail	20%	35%	22%
Male	80%	65%	78%

Average Monthly Expenses
Food	$1,170	$1,018	$1,205
Gas	$90	$69	$70
etc..	$778	$586	$813

sample data:

id	city	gender	AGE	FOOD_dol	GAS_dol
11	1	F	34	34	99
22	1	M	55	55	22
33	1	M	52	66	33
44	2	M	25	77	11
55	2	M	60	77	33
66	2	F	33	88	77

Re: auto report

ballardw — Thu, 16 Apr 2015 23:11:59 GMT

If this is a SAS question there are several approaches one would look like:

proc format;

value $gender 'F'='Female' 'M'='Male';

run;

proc tabulate data=<yourdatasetname>;

class city;

class gender;

format gender $gender.;

var age food_dol Gas_dol;

table age=''*mean='Average age'

Gender *colpctn=''

( Food_dol='Food Expenses' Gas_dol='Gas Expenses' )* sum=''*f=Dollar10. ,

All='Overall' City /

row=float box='Customer Profile';

run;

Re: auto report

Ksharp — Fri, 17 Apr 2015 15:31:12 GMT

Are you doing some Table/Listing about STDM for CRO company ? which make me sick .:smileyangry:

My idea is stacking all these statistical estimator vertically by SQL , and proc transpose it , finally use proc report to get well style report .


data have;
input id     city  gender $     AGE     FOOD_dol     GAS_dol     ;
cards;
11     1     F     34     34     99
22     1     M     55     55     22
33     1     M     52     66     33
44     2     M     25     77     11
55     2     M     60     77     33
66      2     F     33     88     77
;
run;
proc sql;
 create table temp as
  select 1 as order,
         'Customer Profile' as a length=40,
         'Average Age' as b length=40,
         'City 1' as c length=40,
           put(avg(age),best8.) as d length=10
   from have
    where city=1
union all
select 1 as order,
         'Customer Profile' as a length=40,
         'Average Age' as b length=40,
         'City 2' as c length=40,
           put(avg(age),best8.) as d length=10
   from have
    where city=2
union all
select 1 as order,
         'Customer Profile' as a length=40,
         'Average Age' as b length=40,
         'Overall' as c length=40,
           put(avg(age),best8.) as d length=10
   from have
union all
select 2 as order,
         'Gender' as a length=40,
         gender as b length=40,
         'City 1' as c length=40,
           put(count(*)/(select count(*) from have),percent8.2) as d length=10
   from have     
     where city=1
        group by gender
union all
select 2 as order,
        'Gender' as a length=40,
         gender as b length=40,
         'City 2' as c length=40,
           put(count(*)/(select count(*) from have),percent8.2) as d length=10
   from have     
     where city=2
        group by gender
union all
select 2 as order,
        'Gender' as a length=40,
         gender as b length=40,
         'Overall' as c length=40,
           put(count(*)/(select count(*) from have),percent8.2) as d length=10
   from have     
        group by gender
order by order,a,b;
quit;

proc transpose data=temp out=report(drop=_name_);
 by order a b;
 id c;
 var d;
run;

Xia Keshan

Re: auto report

Ying — Sat, 18 Apr 2015 11:18:21 GMT

Thank you, Xia! To creating ORDER variable is good idea!!