topic Re: proc summary procedure giving difference in sum when summarized by different classes in SAS Procedures

proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 03:12:00 GMT

Hello All,

I have a input SAS dataset say insasds with 6 Million records and have 16 variables (1 variable in them has dollars and is summed up), say var1 to var16 (where var16 has the dollars).

I have two summarizations running in my SAS program as shown below.

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is sorted by the class variables (var1 to var10)

BY var1 var2 var3 var4 var5 var6...... var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

and another summarization

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is again sorted here by the class variables (var11 to var15)

BY var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

Now my issue is when I use the PROC TABULATE procedure to generate the total dollars value, what I am seeing is the total dollars value in TEMP1 and TEMP2 is not equal.

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

My understanding of the SUMMARY procedure is it makes the arrangement based on the BY/CLASS variables but the final dollar value would be same. The program has been running fine now for almost 3 years now and the two summarizations used to yield the same total dollars. From the last couple of runs, I am seeing that tabulate procedure for TEMP1 produces some $X and TEMP2 gives $Y where $X and $Y are not same.

Could anyone please advise from where I need to start looking at the problem?

Thanks in Advance!!

Regards,

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Fri, 03 Aug 2012 03:36:49 GMT

Can you post a sample dataset that results with the discrepancy you mentioned? Also, post the code you used to sort the data for each analysis, as that could explain the discrepancy.

Re: proc summary procedure giving difference in sum when summarized by different classes

Cynthia_sas — Fri, 03 Aug 2012 14:12:52 GMT

Hi:

And what you've posted for code doesn't make sense, to me. You call your variables VAR1-VAR10 and VAR11-VAR15 CLASS variables, but you ONLY list them in a BY statement, not a CLASS statement. That doesn't make sense. PROC MEANS would give you different results with a BY versus a CLASS. Consider the following code and the differences between using just the BY and using a CLASS statement. With the BY statement, we just get 1 observation in the output. With the CLASS statement, you get 1 obs for every unique combination of the CLASS variables.

Since you say you have millions of obs, I'm not sure why you are taking this approach anyway (doing a MEANS and then following it with a TABULATE). Especially since TABULATE is capable of generating a summary for your CLASS variables directly without the MEANS step. I'd recommend that you work with Tech Support since you probably can't post millions of records.

cynthia

proc means data=sashelp.class nway missing;

by age sex;

var weight;

output out=work.mns1 sum=;

run;

proc print data=work.mns1;

title 'after proc means 1';

run;

proc means data=sashelp.class nway missing;

class age sex;

var weight;

output out=work.mns2 sum=;

run;

proc print data=work.mns2;

title 'after proc means 2';

run;

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Fri, 03 Aug 2012 15:01:32 GMT

@Cynthia: Either it is me, or the fact that it is Friday, but I disagree. You can't run your first set of code without error since sashelp.class isn't sorted according to the by variables.

If one first uses:

proc sort data=sashelp.class out=class;

by age sex;

run;

and then uses class as the input data for the two means runs, the only difference (I think) will be in the value assigned to _type_.

Re: proc summary procedure giving difference in sum when summarized by different classes

Cynthia_sas — Fri, 03 Aug 2012 15:41:59 GMT

Ah, Art:

You are absolutely correct, _TYPE_ is the only difference. Rookie mistake, I did not check my log & I did not sort and so my print was displaying the results of an entirely different MEANS step (which is a reason for not using the same name over and over!).

I still don't understand the need to have 2 separate PROC MEANS steps prior to TABULATE. I think of TABULATE as being the "GINSU" steak knife of SAS and being able to slice and dice data with great efficiency.

cynthia

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Fri, 03 Aug 2012 15:50:21 GMT

@Cynthia: Let's agree to blame it on being a Friday .. you are definitely NOT a rookie! I'd still like to know why the OP got different results. Many, including me, use means and summary, rather than tabulate, as we find it easier to understand. Someday I really should learn the procedure (along with the other gazillion procedures I don't currently use because they weren't instinctively and almost immediately comprehensible).

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 22:24:11 GMT

Hi Arthur,

PROC SORT DATA=insasds out=insasds.sorted;

BY var1 var2 var3 var4 var5 var6.... var15;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is sorted by the class variables (var1 to var10)

BY var1 var2 var3 var4 var5 var6...... var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted;

BY var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP2 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

This is my code.

Today, I have done an extensive testing by varying the count of observations and here are my observations:

1. Until 3 Million records (out of total universe of 6138735 records), the total var16 dollar value is same in TEMP1 and TEMP2.

2. With 4 Million records, the first difference is seen. the total var16 dollar value is not consisent across TEMP1, TEMP2 SAS Datasets.

3. With the whole universe of 6 Million+ records, the difference increases

I am trying to replace PROC SUMMARY with PROC SQL and use group by and then sum the variable to see if that validates.

Another thing I observed is when I produced a tabular report on insasds.sorted, TEMP1 and TEMP2 for var16. here is what I see.

On insasds.sorted ==> Sum of var16 ==> 77,663,588

On TEMP1 ==> Sum of var16 ==> 77,650,245

On TEMP2 ==> Sum of var16 ==> 77,645,951

Again for 3 Million record obs,

On insasds.sorted ==> Sum of var16 ==> 52,005,676

On TEMP1 ==> Sum of var16 ==> 52,005,676

On TEMP2 ==> Sum of var16 ==> 52,005,676

Please let me know what else details you would like me to elaborate here? I am unable to post a sample dataset because the universe is huge and for small count of observations till 3 Million, the PROC SUMMARY seems to work fine and the total dollars are consistent across input, TEMP1, TEMP2 output files.

Could it indicate rounding off the dollar values (dollars with decimal values) that is creating the difference?

Thank You!!

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 22:36:19 GMT

Hello Cynthia,

The two summarizations are because they form the feed to two different systems. And Tabular report is for dollar validation.

Based on your example, I changed PROC MEANS into PROC SUMMARY and here is my output. My understanding in using the BY and CLASS statements in PROC SUMMARY is both will generate the same result. In the case of BY statement, it is expected the dataset being summarized is sorted.

In the case of CLASS statement, dataset need not be sorted.

Please correct me if I am wrong. This is based on my observations till now.

proc sort data=sashelp.class out=sash; by age sex; run;

proc summary data=sash nway missing;

by age sex;

var weight;

output out=work.mns1 sum=;

run;

proc print data=work.mns1;

title 'after proc means 1';

run;

proc summary data=sashelp.class nway missing;

class age sex;

var weight;

output out=work.mns2 sum=;

run;

proc print data=work.mns2;

title 'after proc means 2';

run;

	after proc means 1
Obs	Age	Sex	_TYPE_	_FREQ_	Weight

1	11	F	0	1	50.5
2	11	M	0	1	85.0
3	12	F	0	2	161.5
4	12	M	0	3	310.5
5	13	F	0	2	182.0
6	13	M	0	1	84.0
7	14	F	0	2	192.5
8	14	M	0	2	215.0
9	15	F	0	2	224.5
10	15	M	0	2	245.0
11	16	M	0	1	150.0

	after proc means 2
Obs	Age	Sex	_TYPE_	_FREQ_	Weight

1	11	F	3	1	50.5
2	11	M	3	1	85.0
3	12	F	3	2	161.5
4	12	M	3	3	310.5
5	13	F	3	2	182.0
6	13	M	3	1	84.0
7	14	F	3	2	192.5
8	14	M	3	2	215.0
9	15	F	3	2	224.5
10	15	M	3	2	245.0
11	16	M	3	1	150.0

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Fri, 03 Aug 2012 22:37:42 GMT

HK: Your problem is in your proc sort! The one proc sort won't work for both proc summaries.

Try it by either substituting class instead of by, thus not requiring a sorted file or, on the other hand, separate proc sorts. In either case, you can reduce the code by specifying variable lists. e.g.:

PROC SORT DATA=insasds out=insasds.sorted;

BY var1-var10;

run;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is sorted by the class variables (var1 to var10)

BY var1-var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

run;

PROC SORT DATA=insasds out=insasds.sorted;

BY var11-var15;

run;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted;

BY var11-var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

run;

Let us know if that fixes the discrepancy.

Art

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Sat, 04 Aug 2012 04:53:01 GMT

Hi Arthur,

Thanks for the suggestion, I replaced BY with CLASS and removed sort. The result is even more weird now :-(... I am beginning to suspect that it could be a case of rounding issue.

PROC SUMMARY NWAY MISSING DATA=insasds;

CLASS var1 var2 var3 var4.....var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

run;

PROC SUMMARY NWAY MISSING DATA=insasds;

CLASS var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

run;

Is my understanding correct? Will the grand total dollars of var16 in insasds, TEMP1 and TEMP2 be same irrespective of the way they are summarized?

Current Results:

PROC TABULATE NOSEPS MISSING DATA=insasds FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP2 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

Tabular proc for insasds: 77,757,256

Tabular proc for TEMP1: 77,648,952

Tabular proc for TEMP2: 77,676,511

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Sat, 04 Aug 2012 15:34:13 GMT

On a couple of occasions, now, you have listed the following as your code:

CLASS var1 var2 var3 var4.....var10;

Is that really how the statement looks? That wouldn't run. You either have to spell the variables out, or use them in a variable list (like var1-var10).

In any case, yes, all of your results should have the same number. I doubt if it is rounding error causing that large of a discrepancy.

Can you post copies of temp1 and temp2?

Re: proc summary procedure giving difference in sum when summarized by different classes

Tom — Sat, 04 Aug 2012 19:36:01 GMT

Sounds like you might be having trouble with orders of magnitude issues. If you try to add a small number to a really large number floating point representation causes the small number to get lost. By adding the numbers in different order then different numbers are being eliminated by this process.

How many observations do you get when you sum by VAR1 - VAR10? What about using VAR11-VAR15?

Run PROC UNIVARIATE on the sums generated into TEMP1 and TEMP2. Do the sums vary widely?

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Sat, 04 Aug 2012 21:20:33 GMT

Hi Arthur,

Apologies for creating a confusion! Yes, I am spelling out the VAR1 to VAR10 variables separately.

CLASS var1 var2 var3 var4 var5 var6 var7 var8 var9 var10;

and actually in my program these variables have more meaningful names (not as var1 to var15).

I don't know if I can post the data here. The summary gives 35933 observations in TEMP1 and 23093 observations in TEMP2.

Hi Tom,

Can you please give an example? I am a beginner with the PROC UNIVARIATE procedure. I have used the var statement as shown below.

PROC UNIVARIATE DATA=TEMP1;

VAR var16;

WEIGHT var16;

RUN;

and please note the program has been giving consistent amount summary on TEMP1 and TEMP2 till last 3 years with around 5 Million observations.

Results of PROC UNIVARIATE.

on insasds

	Weight: var16
	Weighted Moments
N	6138735	Sum Weights	1.0468E22
Mean	2.21819E21	Sum Observations	2.322E43
Std Deviation	3.78917E28	Variance	1.43578E57
Skewness	199.575324	Kurtosis	1537338.08
Uncorrected SS	6.03203E64	Corrected SS	8.81388E63
Coeff Variation	1708229227	Std Error Mean	3.70349E17

On TEMP1.

N	35933	Sum Weights	80644396.8
Mean	576248.022	Sum Observations	4.64712E13
Std Deviation	50713325.6	Variance	2.57184E15
Skewness	113.995947	Kurtosis	17152.4143
Uncorrected SS	1.1919E20	Corrected SS	9.24114E19
Coeff Variation	8800.60732	Std Error Mean	5647.2237

ON TEMP2

N	23093	Sum Weights	84646161.4
Mean	637189.816	Sum Observations	5.39357E13
Std Deviation	48992853	Variance	2.4003E15
Skewness	42.4383756	Kurtosis	2982.60987
Uncorrected SS	8.9795E19	Corrected SS	5.54277E19
Coeff Variation	7688.89455	Std Error Mean	5325.11629

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Sat, 04 Aug 2012 23:19:06 GMT

I have to wonder whether your sum is a larger number than your computer can handle. Obviously, not knowing what your data look like, it is difficult to say and (I would think) highly unusual given the scope of the numbers you have reported. However, if even just one of your newer records had an absurdly high number, that could be the problem.

Can you re-run the analyses, but first reducing var 16 by dividing it by 1,000?

Also, why did you use a weight statement in your proc univariate? It would have been more useful if you hadn't done that.

I, for one, would be most interested in seeing your extreme data points. Can you run something like the following and post the results:

ods select ExtremeObs;

proc univariate data=yourfilename;

var var16;

run;

Re: proc summary procedure giving difference in sum when summarized by different classes

econ — Sat, 04 Aug 2012 23:37:46 GMT

maybe he used weight because it is a sampled dataset.

you have to correct for that.

why else would you use it.

Re: proc summary procedure giving difference in sum when summarized by different classes

art297 — Sun, 05 Aug 2012 00:03:48 GMT

He hasn't used it before, which is why I asked. Why else would one use it? One possibility is that they see it in the documentation, don't understand its ramifications, and simply include it as part of their code .. however wrong that may be!

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Mon, 06 Aug 2012 03:26:50 GMT

Hello Arthur,

The system on which the program is running is MVS (Mainframes).

I am beginner with UNIVARIATE function so when TOM suggested to use UNIVARIATE on sums, I used WEIGHT to see how the total dollars was being showed.

I have tried your suggestion of dividing the var16 by 1000 in the input dataset and then using this new input (with var16 value reduced) for summarizations. The dollar difference is now 186.

SASDS No. of Observations Total $

insasds.new 6138735 78,277

TEMP1 35933 78,200

TEMP2 23093 78,014

And yes, by using the VAR statement and your example UNIVARIATE procedure, I do now see that the input sasds has dollars of very high values that I think is falling out of the numeric range (default min and max range of a numeric, when a variable is declared as numeric, SAS takes it of length 8 bytes??)

Actual run with UNIVARIATE procedure:

SASDS No. of Observations Total $

insasds.new 6138735 77,757,256

TEMP1 35933 77,648,952

TEMP2 23093 77,676,511

insasds:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

--------Lowest------- -------Highest-------

Value Obs Value Obs

-1.640145E21 3.47E6 1.0926427E21 3.47E6

-1.640145E21 3.47E6 1.2878341E21 3.47E6

-1.637649E21 3.47E6 1.6401453E21 3.47E6

-1.287872E21 3.47E6 2.5757056E21 3.47E6

-1.287834E21 3.47E6 3.2777946E21 3.47E6

TEMP1:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

-----Lowest----- -----Highest-----

Value Obs Value Obs

-165766 1663 1269577 6439

-129850 1661 1779938 66

-128195 35750 1905396 11274

-128195 35749 2386700 346

-116770 1692 4376822 402

TEMP2:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

-----Lowest----- -----Highest-----

Value Obs Value Obs

-892673 22993 1819878 15431

-516203 11224 1866471 15875

-485833 11225 2171612 4093

-388422 11226 2343286 15303

-371947 22994 3030318 14326

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Mon, 06 Aug 2012 04:14:16 GMT

Hi Arthur,

As I read again and again, I am now getting to understand what the WEIGHT statement is used for! That was completely not needed in my case.

Thanks for putting me in the right direction!

Re: proc summary procedure giving difference in sum when summarized by different classes

Tom — Mon, 06 Aug 2012 04:39:40 GMT

If you are trying to store dollars and cents as decimal fractions you will get issues from rounding as many numbers cannot be represented exactly using binary floating point numbers.

Try converting your stored value from dollars to pennies (and hence to integers) and you might solve your problem.

cents= round(100 * var16,1);

Remember to divide your resulting sums by 100 to convert from pennies back to dollars.

Re: proc summary procedure giving difference in sum when summarized by different classes

ballardw — Mon, 06 Aug 2012 14:47:04 GMT

Also are there any records that have missing values for any of your class variables? The NWAY in proc summary doesn't create a combination of the class variables that include missing. So the totals of two different CLASS statements on the same value when the VAR variable is summed are likely to differ. The larger the difference the larger the difference in the number of records with missing class variables.

Try using a dummy variable with a value of 1 for each record and compare the output.

Use both proc summary and tabulate to compare the sums of the output data sets.

A brief example of what I mean:

data junk;

input c1 c2 c3 var1;

datalines;

1 . 1 1

1 2 1 2

1 2 2 3

. 2 . 3

2 2 2 4

;

run;

proc summary data=junk nway;

class c1 c2 c3;

var var1;

output out= junk2 sum=;

run;

proc summary data=junk nway;

class c1 c3;

var var1;

output out= junk3 sum=;

run;

proc means data=junk2 sum;

title 'First group of class variables';

var var1;

run;

proc means data=junk3 sum;

title 'Second group of class variables';

var var1;

run;