BookmarkSubscribeRSS Feed
Ying
Fluorite | Level 6

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
3 REPLIES 3
ballardw
Super User

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;

Ksharp
Super User

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

Ying
Fluorite | Level 6

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

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
  • 3 replies
  • 1143 views
  • 6 likes
  • 3 in conversation