The SAS Output Delivery System and reporting techniques

auto report

Reply
Contributor
Posts: 28

auto report

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!

OverallCity 1City 2
Customer Profile
Average Age453642
Gender
Femail20%35%22%
Male80%65%78%
Average Monthly Expenses
Food$1,170 $1,018 $1,205
Gas$90 $69 $70
etc..$778 $586 $813

sample data:

idcitygenderAGEFOOD_dolGAS_dol
111F343499
221M555522
331M526633
442M257711
552M607733
66                2 F338877
Grand Advisor
Posts: 9,681

Re: auto report

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;

Grand Advisor
Posts: 9,307

Re: auto report

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

Contributor
Posts: 28

Re: auto report

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

Post a Question
Discussion Stats
  • 3 replies
  • 395 views
  • 6 likes
  • 3 in conversation