Help using Base SAS procedures

proc tabulate calculations and layout help required

Reply
Occasional Contributor
Posts: 6

proc tabulate calculations and layout help required

I am trying to perform what I hope is a simple calculation within proc tabulate but I am stumped.

To give you some background I am trying replicate some work in Excel to automatically produce a report using ods rtf/ods when I take the the number of people registered on a course and the number of people who successfully completed the course to calculate the percentage of those people by age groups.

In Excel I used summarised to create the following pivot table:

Pivot Table 1

acad_yr
age_HESAData2009/102010/112011/122012/132013/14Grand Total
20 & belowSum of total_registrations2750.93189.83606.53514.53197.016258.7
Sum of total_completions1857.82214.42543.42337.02074.011026.6
21 to 25Sum of total_registrations9956.510542.611193.111608.211746.955047.4
Sum of total_completions6461.26986.47479.57513.97545.735986.8
26 to 30Sum of total_registrations12608.112949.013015.112685.312288.763546.2
Sum of total_completions8292.08755.58844.38534.58190.342616.5
31 to 35Sum of total_registrations10561.011140.711574.510958.310595.354829.8
Sum of total_completions7219.27724.48109.17528.07196.337777.1
36 to 40Sum of total_registrations10699.710396.09913.58741.07921.147671.3
Sum of total_completions7516.47441.07112.16162.65489.933722.0
41 to 45Sum of total_registrations9323.09299.89170.38143.87223.043160.0
Sum of total_completions6626.76775.36695.55846.55127.531071.5
46 to 50Sum of total_registrations6500.36592.86664.46035.45535.631328.5
Sum of total_completions4658.54793.74876.34398.63964.122691.2
51 to 55Sum of total_registrations3887.73925.23983.73579.83256.218632.6
Sum of total_completions2777.02892.22888.52615.52338.013511.0
56 to 60Sum of total_registrations2385.72389.52435.42223.71999.211433.5
Sum of total_completions1708.01744.61808.11636.21480.08376.9
61 to 65Sum of total_registrations1778.31800.21783.81555.81365.58283.7
Sum of total_completions1339.51355.01365.21191.01033.56284.2
66 & overSum of total_registrations1403.41454.31412.21373.91341.26985.0
Sum of total_completions991.81065.81045.01042.3992.25137.1
Total Sum of total_registrations 71854.773680.074752.670419.766469.7357176.7
Total Sum of total_completions 49448.051748.352767.048806.045431.5248200.8

Using this pivot table I then create the below table in Excel which is what gets published to a wider audience

[To calculate the percentages for the specific year and age group I do (Sum of total_completions/Sum of total_registrations)*100]


Final Table 1

2009/102010/112011/122012/132013/14
No of registrations%No of registrations%No of registrations%No of registrations%No of registrations%
20 & below1857.867.52214.469.42543.470.52337.066.52074.064.9
21 to 256461.264.96986.466.37479.566.87513.964.77545.764.2
25 & below Total8319.065.59200.867.010023.067.79850.965.19619.764.4
26 to 308292.065.88755.567.68844.368.08534.567.38190.366.6
31 to 357219.268.47724.469.38109.170.17528.068.77196.367.9
26 to 35 Total15511.266.916479.968.416953.468.916062.567.915386.667.2
36 to 407516.470.27441.071.67112.171.76162.670.55489.969.3
41 to 456626.771.16775.372.96695.573.05846.571.85127.571.0
36 to 45 Total14143.070.614216.372.213807.672.412009.171.110617.470.1
46 to 504658.571.74793.772.74876.373.24398.672.93964.171.6
51 to 552777.071.42892.273.72888.572.52615.573.12338.071.8
46 to 55 Total7435.571.67685.973.17764.772.97014.072.96302.171.7
56 to 601708.071.61744.673.01808.174.21636.273.61480.074.0
61 to 651339.575.31355.075.31365.276.51191.076.61033.575.7
66 & over991.870.71065.873.31045.074.01042.375.9992.274.0
56 & over Total4039.372.64165.473.84218.274.93869.675.13505.774.5
Grand Total49448.068.851748.370.252767.070.648806.069.345431.568.3

My work in SAS so far

Using the below code I have been able to replicate Pivot Table 1 but I cannot work out how I can alter the code to be able to create Final Table 1

PROC TABULATE DATA=WORK.E_AND_D_SUMMARY (where = (all = '1'));

  VAR total_completions

          total_registrations;

  CLASS acad_yr / ORDER=UNFORMATTED MISSING;

  CLASS age / ORDER=UNFORMATTED MISSING;

  CLASS ageband / ORDER=UNFORMATTED MISSING;

  TABLE

  /* ROW Statement */

  (ageband *

  (age *(total_registrations * Sum=' ' total_completions * Sum=' ' )

  all = 'Total' *(total_registrations * Sum=' ' total_completions * Sum=' ' ))

  all = 'Total' *(total_registrations * Sum=' ' total_completions * Sum=' ' ))  ,

  /* COLUMN Statement */

  acad_yr  ;

  ;

RUN;

Proc tabulate output

Financial Year derived from course start date
2009/102010/112011/122012/132013/14
Ageband for the Age GroupsAge Groups2750.883189.793606.543514.463199.29
25 & below20 & belowtotal_registrations
total_completions1857.752214.422543.422336.962074.79
21 to 25total_registrations9956.5410542.6311193.0811608.2111788.67
total_completions6461.216986.427479.547513.927546.92
Totaltotal_registrations12707.4213732.4214799.6315122.6714987.96
total_completions8318.969200.8310022.969850.879621.71
26 to 35Age Groups12608.1312949.3313015.0812685.3312347.17
26 to 30total_registrations
total_completions8291.968755.58844.298533.968189.04
31 to 35total_registrations1056111140.7111574.510958.2910633.5
total_completions7219.257724.378109.6275287196.08
Totaltotal_registrations23169.1324090.0424589.5823643.6322980.67
total_completions15511.2116479.8716953.9216061.9615385.12
36 to 45Age Groups10699.7110396.179913.58741.047950.08
36 to 40total_registrations
total_completions7516.3774417112.126162.625489.87
41 to 45total_registrations9323.049299.759170.338143.837259.04
total_completions6626.676775.296695.55846.465127.54
Totaltotal_registrations20022.7519695.9219083.8316884.8715209.12
total_completions14143.0414216.2913807.6212009.0810617.42
46 to 55Age Groups6500.2965936664.426035.425550.08
46 to 50total_registrations
total_completions4658.54793.754876.754399.083963.13
51 to 55total_registrations3887.673925.383983.713579.833262.92
total_completions2776.962892.172888.462615.462338.21
Totaltotal_registrations10387.9610518.3710648.129615.258813
total_completions7435.467685.927765.217014.546301.33
56 & overAge Groups2385.712389.542435.422223.672000.71
56 to 60total_registrations
total_completions17081744.621808.081636.211480
61 to 65total_registrations1778.331800.211783.831555.751365.92
total_completions1339.541354.961365.171191.041033.63
66 & overtotal_registrations1403.371454.291412.211373.921341.08
total_completions991.791065.7910451042.33992.04
Totaltotal_registrations5567.425644.045631.465153.334707.71
total_completions4039.334165.374218.253869.583505.67
Totaltotal_registrations71854.6773680.7974752.6370419.7566698.46
total_completions4944851748.2952767.9648806.0445431.25

I cannot find a way of being able to calculate the percentage of completions against total registrations

Is it possible to get proc tabulate to produce a layout similar to Final Table 1

Thanks in advance

Super User
Posts: 11,338

Re: proc tabulate calculations and layout help required

Without having some data to play with this is a bit difficult. BUT if I understand that your issue is with getting the Age band in the same column and above the ages then proc format should fix the problem. I suspect you have the age as a character value (which while Excel does that it isn't the optimum with SAS as you could have your data numeric and group on the fly just using a different format.

Any way here is a stab:

NOTE order of definition in multilabel formats is important

Proc format;

value $agebanding (multilabel)

'20 & below' ='20 & below'   /* these duplications are needed because you want both the group and individual values*/

'21 to 25'      ='21 to 25' 

'20 & below','21 to 25'='25 & below'

'26 to 30'  = '26 to 30'

'31 to 35'   = '31 to 35' 

'26 to 30','31 to 35' = '26 to 35'

<continue in this fashion>

;

/* if you had age as a numeric */

value agegbanding (multilabel)

0 - 20 = '20 & below'

21 - 25= '21 to 25'

0 - 25 = '25 & below'

26 - 30 = '26 to 30'

31 - 35 = '31 to 35'

26 - 35= '26 to 35'

<and again continue>

;

run;

In your proc tabulate modify the class statement for age

Class age / mlf ;

format age $agebanding.;

And do not use an ALL on age.

You may need to add an ORDER to the CLASS statement for AGE but with out data to test on I'm not sure what it may need to be.

Occasional Contributor
Posts: 6

Re: proc tabulate calculations and layout help required

I was unable to attach data to the thread and I therefore went to SAS to get assistance.

They provided me with the following code which has worked (There have been soime slight changes to the variable names compared to the above)

proc tabulate data=e_and_d_summary (where=(course_group="Undergraduate"));

var total_regftes total_compftes;

class age_hesa  / order=unformatted missing;

class ageband_hesa / order=unformatted missing;

class acad_yr  / order=unformatted missing;

table

  /* Row Dimension */

  (ageband_hesa=""*(age_hesa="" ALL="Total"))

  all = "Total",

  /* column dimension */

  acad_yr="Year"*total_compFTEs=""*(sum="RegFTE No." pctsum<total_regFTEs>="%")

;

run;

Super User
Posts: 10,018

Re: proc tabulate calculations and layout help required

I would suggest to use proc report . Post it at ODS and Base Reporting

And Cynthia will give good advice .

Xia Keshan

Ask a Question
Discussion stats
  • 3 replies
  • 176 views
  • 1 like
  • 3 in conversation