- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi RW9,
Thanks for your advice.
However, I would like have the outcome, something likes this
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You did not include the proc means step which produces the summary.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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:
Personally though I like to get my data looking the right way first, then using a basic proc report output.