topic Summary Report shows different observations in SAS Studio

Summary Report shows different observations

bondtk — Mon, 02 Oct 2017 07:49:50 GMT

I am new to SAS, i imported the excel file by using the Import wizard.

File has 8011 obsevations. I am using SAS studio and SAS EG as well.

Did the data segmentation uisng to capture data in different ranges.

Run Proc freq , which still shows 8011 observations.

Then run the proc summary to make this file as output so that I can export this file as excel file to run some pivot tables/chart.

Proc summary shows i have 5297 observations instead of 8011. Not sure why? if someone can shed some light on it.

My code is:

data orion.aaamonthlydata;
length lend_level $10;
set orion.rawdatamonthly;

if amount_financed LE 75000 then Lend_Level ='Under75k';
else if (Amount_financed > 75000) and (Amount_financed<= 150000) then Lend_Level ='75-150k';
else if Amount_financed > 150000 and (Amount_financed<= 300000) then Lend_Level ='150k-300k';
else Lend_Level ='300k-500k';
run;

proc freq data=orion.aaamonthlydata order=freq;
tables lend_level*system_decision / norow nocol nopercent nocum;
title1 ' Volume Distribution ';
run;

Result: 8011 total observations in the table.

proc summary data=orion.aaamonthlydata nway missing;
class Amount_Financed System_decision lend_level;
output out=orion.volumedist;
run;

Only shows 5291 observations.

Need to know why proc freq shows 8011 observationa and proc summary shows 5297 observations. please advise.

Re: Summary Report shows different observations

RW9 — Mon, 02 Oct 2017 09:01:49 GMT

Its quite hard to say without seeing data, but from your freq:

tables lend_level*system_decision / norow nocol nopercent nocum;

versus your summary:

class Amount_Financed System_decision lend_level;

We can see that there is another variable present - amount_financed which is not included in the freq. Could it be that this is grouping the data in smaller groups, hence you end up with fewer results?

Re: Summary Report shows different observations

ballardw — Mon, 02 Oct 2017 14:49:52 GMT

Your Proc Summary output shown is incomplete. Note that the output says 5 columns but you only show 3.

Yout output data set would contain two other variables if the code you show is what you ran. One variable would be _type_ to indicate the combination of class variables and the other would be _freq_ indicating how many records have that combination of the class variables.

So I suspect that _freq_ has occasional values of 2 or 3 or even more and the TOTAL of _freq_ will match the number of records from proc freq.

Re: Summary Report shows different observations

bondtk — Sun, 08 Oct 2017 07:55:28 GMT

You are right, I though proc summary will show the same number of observations, but it grouped the entries and hence shows less observations, when I export the file in excel and run pivot table it shows the same 8011 as total , so the problem is solved.