topic Re: Create Subtotal & Total in SAS Enterprise Guide

Create Subtotal & Total

SASnewbie2 — Wed, 19 Sep 2018 08:32:25 GMT

Hi Experts,

Appreciate your advice how can i create a subtotal and total in the dataset and export to Microsoft Excel.

Would like to create subtotal for salary by country

Attached herewith the Microsoft Excel output format & SAS Sample Data set

Re: Create Subtotal & Total

RW9 — Wed, 19 Sep 2018 08:08:02 GMT

Post test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

You would use one of the summary procedures - means, summary - and then add that back to your data, something like:

proc means data=have;
  class country;
  var salary;
  output out=subtotal sum=salary;
run;

data want;
  set have salary;
run;

To export to Excel there are numerous methods depending on which version of SAS or which product from SAS you are using, what operating system etc. The question is to vague, proc export, ods tagsets.excelxp, csv output, libname excel, pcfiles etc.

Re: Create Subtotal & Total

SASnewbie2 — Wed, 19 Sep 2018 08:41:27 GMT

Hi RW9,

Thanks for your advice.

However, I would like have the outcome, something likes this

Re: Create Subtotal & Total

RW9 — Wed, 19 Sep 2018 08:53:36 GMT

proc means data=have;
  class country;
  var salary;
  output out=subtotal sum=salary;
run;

data want;
  set have (in=a) salary (in=b);
  if b then country=cat(strip(country)," Total");
run;

proc sort data=want;
  by country employee;
run;

Re: Create Subtotal & Total

SASnewbie2 — Wed, 19 Sep 2018 09:49:16 GMT

34         
35         data want;
36           set WORK.SALES (in=a) salary (in=b);
ERROR: File WORK.SALARY.DATA does not exist.
37           if b then country=cat(strip(country)," Total");
38         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 9 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

39         
40         proc sort data=want;
41           by country employee;
ERROR: Variable EMPLOYEE not found.
42         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
43         
44         %_eg_hidenotesandsource;
56

Hi i tried your code but there's an error when i run it. Anything I have missed out ?

Re: Create Subtotal & Total

RW9 — Wed, 19 Sep 2018 09:52:36 GMT

You did not include the proc means step which produces the summary.

Re: Create Subtotal & Total

SASnewbie2 — Wed, 19 Sep 2018 09:57:14 GMT

1                                                          The SAS System                        17:05 Wednesday, September 19, 2018

1          %_eg_hidenotesandsource;
5          %_eg_hidenotesandsource;
28         


29         proc means data=WORK.SALES;
30           class country;
31           var salary;
32           output out=subtotal sum=salary;
33         run;

NOTE: There were 165 observations read from the data set WORK.SALES.
NOTE: The data set WORK.SUBTOTAL has 3 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.16 seconds
      cpu time            0.04 seconds
      

34         
35         data want;
36           set WORK.SALES (in=a) salary (in=b);
ERROR: File WORK.SALARY.DATA does not exist.
37           if b then country=cat(strip(country)," Total");
38         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 9 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

39         
40         proc sort data=want;
41           by country employee;
ERROR: Variable EMPLOYEE not found.
42         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
43         
44         %_eg_hidenotesandsource;
56         
57         
58         %_eg_hidenotesandsource;
61

Hi RW9,

I did, kindly refer to screenshot below.

Re: Create Subtotal & Total

RW9 — Wed, 19 Sep 2018 09:58:14 GMT

proc means data=have;
  class country;
  var salary;
  output out=subtotal sum=salary;
run;

data want;
  set have (in=a) subtotal (in=b);
  if b then country=cat(strip(country)," Total");
run;

proc sort data=want;
  by country employee;
run;

Re: Create Subtotal & Total

SASnewbie2 — Wed, 19 Sep 2018 09:59:57 GMT

                                                          The SAS System                        17:05 Wednesday, September 19, 2018

1          %_eg_hidenotesandsource;
5          %_eg_hidenotesandsource;
28         


29         proc means data=have;
ERROR: File WORK.HAVE.DATA does not exist.
30           class country;
ERROR: No data set open to look up variables.
31           var salary;
ERROR: No data set open to look up variables.
32           output out=subtotal sum=salary;
33         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SUBTOTAL may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.SUBTOTAL was not replaced because this step was stopped.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds
      
34         


35         data want;
36           set have (in=a) subtotal (in=b);
ERROR: File WORK.HAVE.DATA does not exist.
37           if b then country=cat(strip(country)," Total");
38         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations and 4 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      

39         
40         proc sort data=want;
41           by country employee;
ERROR: Variable EMPLOYEE not found.
42         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
43         
44         %_eg_hidenotesandsource;
56         
57         
58         %_eg_hidenotesandsource;
61

Sorry RW9, still can't

Re: Create Subtotal & Total

RW9 — Wed, 19 Sep 2018 10:07:24 GMT

You have not replaced the dataset in the means.

proc means data=have;   <<<<Replace have with your data here
  class country;
  var salary;
  output out=subtotal sum=salary;
run;

data want;
  set have (in=a) subtotal (in=b); <<<<Replace have with your data here
  if b then country=cat(strip(country)," Total");
run;

proc sort data=want;
  by country employee;
run;

Please also refer to the log which clearly tells you where the problem is:

ERROR: File WORK.HAVE.DATA does not exist.

Re: Create Subtotal & Total

SASnewbie2 — Thu, 20 Sep 2018 08:01:31 GMT

Hi RW9

Thanks for your advice

Here is the the output which produced by SAS.

However, I would like to have a data set with subtotals as shown below. Just wonder whether possible to do it from SAS ?

Re: Create Subtotal & Total

RW9 — Thu, 20 Sep 2018 08:27:30 GMT

That is just the default output from proc means, you can switch it off by adding noprint in as below. The dataset created at the end of the step, contians the data need to create your report, you would then proc report that data using your style:

proc means data=have noprint;   <<<<Replace have with your data here
  class country;
  var salary;
  output out=subtotal sum=salary;
run;

data want;
  set have (in=a) subtotal (in=b); <<<<Replace have with your data here
  if b then country=cat(strip(country)," Total");
run;

proc sort data=want;
  by country employee;
run;

ods pdf file="....pdf";

proc report...;
  ...;
run;

ods pdf close;

Obviously replace the elipses with the necessary information, file path/name, and the proc report code. Could be as simple as:

proc report data=want nowd;
  columns _all_;
run;

That will give basic output, likely you will need a computed style call though for highlighting the totals, maybe something like:

proc report data=want nowd;
  columns _all_;
  
  compute country;
    if index(country,"Total") then call define(_row_,'style','style=[font_weight=bold]');
  endcomp;
run;

Something like that, you will have to play with it. There is a lot of information out there on proc report, which is a huge procedure, you may even be able to compute your subtotals directly in that procedure:

https://communities.sas.com/t5/SAS-Procedures/How-to-Label-totals-and-subtotals-in-PROC-Report/td-p/430403

https://communities.sas.com/t5/SAS-Procedures/proc-report-subtotal-when-using-group-noprint/td-p/124710

Personally though I like to get my data looking the right way first, then using a basic proc report output.