topic proc tabulate sum totals in SAS Enterprise Guide

proc tabulate sum totals

Thokozani — Fri, 18 Mar 2016 09:35:59 GMT

Hi All,

Can you please assist.

I looking for a way to generate a report as per attached spreadsheet. I want to add total employee + total third party.

Please find a sample.xlsx file.

Regards,

Thokozani

Re: proc tabulate sum totals

RW9 — Fri, 18 Mar 2016 09:56:09 GMT

Hi,

If you would like accurate answers, post test data in the form of a datastep so we have something to work with. Personally I tend to code these use SQL:

proc sql;
  create table WANT as
  select  COUNTRY,
          sum(PERM),
          sum(CONTRACT),
          sum(TEMP),
          count(EMPLOYEES),
          count(AGENCY),
          count(CONSULTANTS),
          count(AGENCY)+count(CONSULTANTS)
  from    HAVE
  group by COUNTRY
  union all
  select  "Region Total",
          sum(PERM),
          sum(CONTRACT),
          sum(TEMP),
          count(EMPLOYEES),
          count(AGENCY),
          count(CONSULTANTS),
          count(AGENCY)+count(CONSULTANTS)
  from    HAVE;
quit;

Re: proc tabulate sum totals

Thokozani — Fri, 18 Mar 2016 10:33:07 GMT

Hi,

Please find my proc tabulate code.I want to add another column that will*
sum=Employee Total+Total Third Party*

PROC TABULATE
DATA=REPORT.SEA_OPCO FORMAT=10. ;
VAR total_employee2 total_employee3 x;
CLASS employee_person_type3 / DESCENDING MISSING;
CLASS Operation employee_person_type2/ MISSING s=[background=yellow];
class dummy / MISSING s=[background=black];
TABLE
/* ROW Statement */
Operation = '' all = ' Region Total',
/* COLUMN Statement */
Employee_Person_Type3='' *(total_employee3=' ' * Sum={LABEL=" "} )
all = ' Employee Total' *(total_employee3=' ' * Sum={LABEL=" "} )
dummy=' '*x=''*sum=''*{style={background=black }}
Employee_Person_Type2='' *(total_employee2=' ' * Sum={LABEL=" "} )
all = ' Total Third Party' *(total_employee2=' ' * Sum={LABEL=" "}
)
/ misstext='0' box=[label="OpCo" style=[background=yellow]];
keyword all /s=[background=yellow];

RUN;

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: proc tabulate sum totals

Reeza — Fri, 18 Mar 2016 11:07:26 GMT

If I understand correctly you can't calculate new variables in proc tabulate. You can use proc report for calculations or a data step.