Help using Base SAS procedures

Merging Summary Statistics with the Original Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Merging Summary Statistics with the Original Data

I am learning about how to merge summary statistics with the original data in order to compute fraction values which need e.g. an overall mean or a mean per category. It could also be done e.g. with proc sql, but my problem is not the logic, but why I get at the final report a line I do not want to have.

I have a data set "mergesummary":

Max Flight          running 1930

Zip Fit Leather    walking 2250

Zoom Airborne    running 4150

Light Step          walking 1130

Max Step Woven walking 2230

Zip Sneak          c-train  1190

I read this data with an infile and input statement. Then I sort it:

data mergesummary;

    infile 'D:\SAS\running.txt';

    input stylename $15. +1 typeex $7. +1 totalsales 4.;

run;

proc sort data=mergesummary;

    by typeex;

run;

Then I calculate the summaries and use them to output the SUM of totalsales into a new data set "summarydata":

proc means NOPRINT data=mergesummary ;

    var totalsales;

    class typeex;

    output out=summarydata SUM(totalsales)=Totalcategory;

run;

Now I sort again:

proc sort data=summarydata;

    by typeex;

run;

Then I merge the original data with the summary data set:

data finareport;

    merge mergesummary summarydata;

    by typeex;

    PercentageEach=totalsales/totalcategory;

run;

It all works fine, so I have no problem here!

Then I use a proc print to show the results:

proc print data=finareport;

    by typeex;

    ID typeex;

    var stylename totalsales totalcategory percentageeach;

    format PercentageEach Percent9.3;

run;

This looks like:

At the beginning the first row is just showing the total sum of all sales (the variable unfortunately is called totalsales, so it shows the total sum of totalsales). This is due to the fact that is has been calculated and output to the "summarydata" set. However, due to matching (since there is no match and it has no typeex entry) I thought it will drop out. I have the same solution in a book and there the line is not shown? But they use the same code?

Where is my mistake and how can I get rid of this row?


Accepted Solutions
Solution
‎04-21-2015 09:01 AM
Super User
Super User
Posts: 7,997

Re: Merging Summary Statistics with the Original Data

Posted in reply to PaigeMiller

Would replacing class with by also work?

View solution in original post


All Replies
Trusted Advisor
Posts: 1,934

Re: Merging Summary Statistics with the Original Data

It comes from PROC MEANS, if you don't want it use the NWAY option in PROC MEANS

There's no need to do a sort before your PROC MEANS.

Solution
‎04-21-2015 09:01 AM
Super User
Super User
Posts: 7,997

Re: Merging Summary Statistics with the Original Data

Posted in reply to PaigeMiller

Would replacing class with by also work?

Occasional Contributor
Posts: 8

Re: Merging Summary Statistics with the Original Data

Wow! Nice one! This is EXACTLY my mistake! I used a class statement, but the book uses a by statement. What's the reason for this? Why does class give me this and by not?

Super User
Super User
Posts: 7,997

Re: Merging Summary Statistics with the Original Data

Third from last section in here:

Base SAS(R) 9.2 Procedures Guide

Yes, I have always used by statements in means until recently someone stated to use class.  They are similar but have differing options.

Trusted Advisor
Posts: 1,934

Re: Merging Summary Statistics with the Original Data

CLASS doesn't require pre-sorting of the data, and it gives you all possible comibnations of the class variables, including the "null combination" which is the statistics for all of your data regardless of the level of the class variables. That is the first line that you see in your output from PROC MEANS, it is the sum across all of the data. The NWAY turns off this feature and only gives you the sums for each level of the class variables. In a lot of ways, CLASS is a much more powerful tool than using BY in PROC MEANS. CLASS does everything that BY does, so BY is a subset of CLASS.

Super User
Posts: 11,343

Re: Merging Summary Statistics with the Original Data

Posted in reply to PaigeMiller

And an occasionally beneficial side effect with NWAY is the data on output is sorted by the class variables.

Occasional Contributor
Posts: 8

Re: Merging Summary Statistics with the Original Data

Posted in reply to PaigeMiller

Thanks a lot that helped! However this is not included in the book solution... but thanks!

I have to sort before proc means, not because of proc means, but because later on I use a data merge with a by statement.

Super User
Posts: 11,343

Re: Merging Summary Statistics with the Original Data

Maybe this gets close to what you want

proc tabulate data=mergesummary;

class typeex;

class stylename;

var totalsales;

table typeex*stylename,

      totalsales=''*(sum='Total Sales'*f=best6. pctsum<stylename>='Percentage Each'*f=best7.);

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 542 views
  • 12 likes
  • 4 in conversation