SAS and sum

[SPhill]

I'm trying to do something which should be simple but am having problems with, basically using the underneath table I need to sum my 'unitts' field so it will give me a sum by polref inrref covref funds so obs 5 will show '249.57'.

PROC MEANS, PROC PRINT, with by statements work but I need the same type of format as below not subtotals, I've not had success with PROC SUMMARY.

Anyone have any suggestions??



Obs polref inrref covref funds unitts

1 91511753 1 1 640 1146.65
2 91511753 2 1 640 1433.36
3 91574053 1 1 637 63.58
4 91574053 1 1 641 -0.65
5 91574053 1 1 641 250.22
6 91574053 2 1 637 63.58
7 91574053 2 1 641 -0.65
8 91574053 2 1 641 250.22
9 91628753 1 1 628 297.65
10 91628753 2 1 628 148.83

 

[klaz2002]

Sphil,
Are you trying to calculate a running total (current balance) or are you interested in the final sum by polref?

If your answer is a final sum by polref, before any proc means or print why not use the SQL procedure to do the stats for you.
Here try this code;

Proc SQL;
  create table sumstats as 
  select polref,sum(unitt) as Total
  from yourtable
  group by polref;
quit;

Hope this helps.
Klaz

 

[SPhill]

Thanks Klaz, this works:-

Proc SQL;
create table sumstats as
select polref,inrref,covref,funds,sum(unitts) as Total
from mytable
group by polref,inrref,covref,funds;
quit;
*;

and produces this:-

Obs polref inrref covref funds Total

1 91511753 1 1 640 1146.65
2 91511753 2 1 640 1433.36
3 91574053 1 1 637 63.58
4 91574053 1 1 641 249.57
5 91574053 2 1 637 63.58
6 91574053 2 1 641 249.57
7 91628753 1 1 628 297.65
8 91628753 2 1 628 148.83

I had a hunch it was PROC SQL or ARRAY, I'm suprised SAS doesn't allow you to do something like

SUM unitts;
BY polref inrref covref funds;
RUN;

in an ordinary datastep tho??

 

[klaz2002]

You can do it in a datastep by use of the RETAIN statement.
Klaz

 

[ChrisW75]

Sphill - You can also do it using either PRoc Summary or Proc Means.

proc summary data=mydat nway;
  by polref inrref covref funds;
  var unitts;
  output out=summed(drop=_freq_ _type_) sum=total;
run;

The Sum=tells it what to call the summed column (listed under VAR). If you just put sum= it'll sum the column up and call it the original name again. The only difference between proc summary and proc means is that proc means produces printed output by default and proc summary doesn't. The NWAY option tells SAS to group by all 4 of the BY variables, without it, it'll group by every possible combination of the 4 variables, the _type_ variable is used to determine which grouping you are looking at on a record.
Look up MEANS in the doco for all the options (the doco for summary just tells you to look at MEANS).

Enjoy.

 

[SPhill]

Thanks guys, this has helped a lot.

SP