Lesson 15, Part 1: Extra SAS Code (Adding Summary Data with Detailed Data)¶

In [2]:
/*Create the total row*/
options nocenter nodate nonumber nosource;
proc means noprint data=sashelp.cars ;
  output out=summary(keep=mpg_city) sum=mpg_city;
run;

/*Create the necessary table using proc sql*/
proc sql;
	create table test as
		select make, count(make) as count
		from sashelp.cars
		group by make
		order by make;
quit;

/*Combine the tables*/
data want ;
  set test summary(in=in2); /*concatenate the tables. In= option creates a temp variable called in2*/
  if in2=1 then Make='TOTAL'; /*In2 will =1 when SAS brings in the row from summary. This will allow us to change the column name we want to "Total"*/
run;
proc print data=want;
run;
SAS Output

The SAS System

Obs Make count mpg_city
1 Ford 1 .
2 GMC 1 .
3 Hummer 1 .
4 TOTAL . $137,735
In [8]:
proc sql;
    select make, count(make) as count
    from sashelp.cars
    group by make
    UNION
    select 'Total' as make, 
           count(*) as count format=comma14.
    from sashelp.cars
    order by case when make = 'Total' then 1 else 0 end,
             make;
quit;

proc sql;
    select make, 
           count(make) as count,
           (count(make) / (select count(*) 
            from sashelp.cars)) * 100 as percentage format=5.2
    from sashelp.cars
    group by make
    
    UNION
    
    select 'Total' as make, 
           count(*) as count,
           100.00 as percentage format=5.2
    from sashelp.cars
    
    order by case when make = 'Total' then 1 else 0 end,
             make;
quit;
SAS Output

The SAS System

Make count
Ford 1
GMC 1
Hummer 1
Total 3

The SAS System

Make count percentage
Ford 1 33.33
GMC 1 33.33
Hummer 1 33.33
Total 3 100.0
In [10]:
proc report data = sashelp.cars nowd;
 column make n pctn;
 define make /group id order = internal;
 define n / format =8. "N";
 define pctn / "Percentage" format =percent7.1;
 rbreak after /summarize style=[font_style=italic];
 compute after ;
	  make='Total';
endcomp;
run;
SAS Output

The SAS System

Make N Percentage
Ford 1 33.3%
GMC 1 33.3%
Hummer 1 33.3%
Total 3 100%
In [ ]:
proc format;
value $regionfmt
    'AFR' = 'Africa'
    'AMR' = 'Americas'
    'EUR' = 'Europe'
    'EMR'  ='Eastern Mediterranean'
    'SEAR' = 'South-East Asia'
    'WPR' = 'Western Pacific';

invalue order            
    'AFR' = 1
    'AMR' = 2
    'EUR' = 3
    'EMR'  = 4
    'SEAR' = 5
    'WPR' = 6
    other = 7; 
run;

* Method 1 - Data step approach;
proc sort data=sashelp.demographics out=demographics; 
  by region; run;
data want1(keep= region countries sum_pop);
  set demographics;
  by region;
  if first.region then do;
    sum_pop=pop;
    countries=1;
  end;
  else do;
    sum_pop+pop;
    countries+1;
  end;
  if last.region then output;
 run;

  data want1x;
    set want1 end=lastobs;
	total + sum_pop;
	if lastobs then do;
     call symputx('total', total);
	output;
 run;
%put &=total;

 data running_pct;
  set want1;
  running_pct = sum_pop/&total;
run;

Title "Summarization Method 1 (DATA STEP Approach)";
proc print data=running_pct;
var region countries sum_pop running_pct;
sum countries sum_pop running_pct;
format region $regionfmt. sum_pop comma14. running_pct percent8.1;
run;
In [ ]:
* Method 2;
proc means  data=sashelp.demographics noprint;
  var pop;
  output out=summary2(drop=_:) N= countries sum= sum_pop;
run;

proc means data=sashelp.demographics noprint nway;
  class region;
  var pop;
  output out=want2(drop=_:) N=countries sum=sum_pop;
run;

data want2x ;
  set want2 summary2(in=in2);
  if in2=1 then region='TOTAL'; 
if region='TOTAL' then call symputx('total2', sum_pop);
run;

%put &=total2;
data running_pct2;
  set want2x;
  running_pct = sum_pop/&total2;
run;
%put &=total2;

Title "Summarization Method 2 (PROC MEANS)";
proc print data=running_pct2;
format region $regionfmt. sum_pop comma14. running_pct percent8.1;
run;
In [ ]:
* Method 3;

proc summary data=sashelp.demographics ;
  var pop;
  output out=summary3 (drop=_:) 
           n=Countries
           sum=sum_pop;
run;

proc summary data=sashelp.demographics nway;
  var pop;
  class region;
  output out=want3 
           n=Countries
           sum=sum_pop;
run;
data want3x (drop=_:);
  set want3 summary3(in=in3);
  if in3=1 then region='TOTAL'; 
   if region='TOTAL' then call symputx('total3', sum_pop);
run;
%put &=total3;

 data running_pct3;
  set want3x;
  running_pct = sum_pop/&total3;
run;
%put &=total3;

Title "Summarization Method 3 (PROC SUMMARY)";
proc print data=running_pct3;; 
format region $regionfmt. sum_pop comma14. running_pct percent8.1;
run;
In [ ]:
* Method 4; 
Title "Summarization Method 4 (PROC TABULATE)";
proc tabulate data=sashelp.demographics ;
  class region;
  var pop;
  tables  region all, pop*(N*f=4.0 sum*f=comma14. pctsum*f=7.1);
  format region $regionfmt.;
run;
In [ ]:
* Method 5;
Title "Summarization Method 5 (PROC REPORT)";
ods listing;
proc report data=sashelp.demographics  headline headskip;
  column region pop pop=sum pop=pct;
  define region / group format=$regionfmt. ;
  define pop / analysis 'N'  n;
  define sum / analysis 'Sum'  format=comma14.;
  define pct / analysis 'Percent of Total' pctsum format=percent8.1;
  
  compute after;
      region = 'Total';
  endcomp;
 rbreak after / skip summarize ;
run;
In [ ]:
* Method 6;
Title "Summarization Method 6 (PROC SQL)";
proc sql;
create table want1 as(
select region format= $regionfmt.,
      sum(pop) as sum_pop format=comma14.,
	  sum(pop)/ (select sum(pop) from  sashelp.demographics)*100 as percent_pop format=8.1
    from 
        sashelp.demographics
    group by region 
	
                      )
  union 
 select 'Total', 
         sum(pop) as sum_pop format=comma14.,
         sum(pop)*100/sum(pop) as percent_pop format=8.1
   from sashelp.demographics;
  select * 
   from want1
   order by input(region, order.); 
quit;
In [ ]:
* Method 7;
proc sort data=sashelp.demographics out=demographics; 
  by region; 
run;

data want1(keep=region countries sum_pop);
  if _n_ = 1 then do;
    declare hash h(ordered:'a');
    h.definekey('region');
    h.definedata('region', 'countries', 'sum_pop');
    h.definedone();
  end;
  
  set demographics end=eof;
  by region;
  
  if h.find() ne 0 then do;
    countries = 1;
    sum_pop = pop;
  end;
  else do;
    countries + 1;
    sum_pop + pop;
  end;
  
  h.replace();
  
  if last.region or eof then do;
    h.find();
    output;
  end;
run;
data want1x;
  set want1 end=lastobs;
  total + sum_pop;
  if lastobs then do;
    call symputx('total', total);
    output;
  end;
run;

%put &=total;

data running_pct;
  set want1;
  running_pct = sum_pop/&total;
run;

Title "Summarization Method 7 (Hash Object Approach)";
proc print data=running_pct;
  var region countries sum_pop running_pct;
  sum countries sum_pop running_pct;
  format region $regionfmt. sum_pop comma14. running_pct percent8.1;
run;
In [ ]:
Title "Summarization Method 8 (PROC IML Approach)";
proc iml;
    /* Read the data */
    use sashelp.demographics;
    read all var {"Region" "Pop"};
    close sashelp.demographics;

    /* Get unique regions */
    unique_regions = unique(Region);
    n_regions = ncol(unique_regions);

    /* Initialize result matrices */
    result_n = j(1, n_regions, 0);
    result_sum = j(1, n_regions, 0);

    /* Calculate N and Sum for each region */
    do i = 1 to n_regions;
        region_mask = (Region = unique_regions[i]);
        result_n[i] = sum(region_mask);
        result_sum[i] = sum(Pop # region_mask);
    end;
    
    /* Calculate percentages */
    total_sum = sum(result_sum);
    result_pct = result_sum / total_sum;

    /* Create final result matrices */
    region_col = (unique_regions || "Total")`;
    n_col = (result_n || sum(result_n))`;
    sum_col = (result_sum || total_sum)`;
    pct_col = (result_pct || 1)`;

    /* Create column names */
    col_names = {"Region" "N" "Sum" "Percent_of_Total"};

    /* Print results */
    *print region_col n_col sum_col pct_col[colname=col_names];

    /* Create a dataset for PROC PRINT */
    create work.result var {region_col n_col sum_col pct_col};
    append;
    close work.result;
quit;

proc print data=work.result noobs label;
    var region_col n_col sum_col pct_col;
    label region_col = "Region"
          n_col = "N"
          sum_col = "Sum"
          pct_col = "Percent of Total";
    format n_col comma8. sum_col comma14. pct_col percent8.1;
run;

PROC PRINTTO;
RUN;
In [12]:
*CallSymputx.sas;

proc means data=sashelp.class mean maxdec=1 noprint;
 var weight;
 output out=stats (drop = _TYPE_ _FREQ_) mean=average_wgt;
run;

data _null_;
  set stats;
  call symputx('average_wgt', average_wgt);
 run;

Data test;
 set SASHELP.class;
  weight_ratio=weight/&average_wgt;  
run;
proc print data=test;
run;
SAS Output

The SAS System

Obs Name Sex Age Height Weight weight_ratio
1 Alfred M 14 69.0 112.5 1.12470
2 Alice F 13 56.5 84.0 0.83978
3 Barbara F 13 65.3 98.0 0.97974
4 Carol F 14 62.8 102.5 1.02473
5 Henry M 14 63.5 102.5 1.02473
6 James M 12 57.3 83.0 0.82978
7 Jane F 12 59.8 84.5 0.84478
8 Janet F 15 62.5 112.5 1.12470
9 Jeffrey M 13 62.5 84.0 0.83978
10 John M 12 59.0 99.5 0.99474
11 Joyce F 11 51.3 50.5 0.50487
12 Judy F 14 64.3 90.0 0.89976
13 Louise F 12 56.3 77.0 0.76980
14 Mary F 15 66.5 112.0 1.11971
15 Philip M 16 72.0 150.0 1.49961
16 Robert M 12 64.8 128.0 1.27966
17 Ronald M 15 67.0 133.0 1.32965
18 Thomas M 11 57.5 85.0 0.84978
19 William M 15 66.5 112.0 1.11971
In [ ]:
In [ ]: