SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
SASnewbie2
Fluorite | Level 6

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

 

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

 

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.

SASnewbie2
Fluorite | Level 6

Hi RW9,

 

Thanks for your advice.

 

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

 

SAS.png

RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;
SASnewbie2
Fluorite | Level 6
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 ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

SASnewbie2
Fluorite | Level 6
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.

 

sas.png

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;
SASnewbie2
Fluorite | Level 6
                                                          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 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.
SASnewbie2
Fluorite | Level 6

Hi RW9

 

Thanks for your advice 

 

Here is the the output which produced by SAS. 

 

SAS 1.png

 

 

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

 

SAS.png

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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/...

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

 

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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 7847 views
  • 0 likes
  • 2 in conversation