Desktop productivity for business analysts and programmers

proc tabulate sum totals

Reply
Occasional Contributor
Posts: 5

proc tabulate sum totals

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

 

 

Super User
Super User
Posts: 7,710

Re: proc tabulate sum totals

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;
Occasional Contributor
Posts: 5

Re: proc tabulate sum totals

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. -##
Super User
Posts: 19,064

Re: proc tabulate sum totals

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

Ask a Question
Discussion stats
  • 3 replies
  • 394 views
  • 0 likes
  • 3 in conversation