How do I sum values by year

[anner]

I am trying to do something that I think should be easy but I can't get it to work.  I am trying to create a table that shows the total of inf, by year (estyr) and county (cname).  Here is my code.  It is totaling the year and totaling the inf.  Please help, attached is my code which combines 4 different files (one per calendar year).

Thank you in advance for any help you can provide.

Anne

[PaigeMiller]

Do not put code (or anything else) into Microsoft Office documents, as many of us will not download it because it is a security threat.

Instead, copy the code as text, and then click on the running man icon here and paste it into the window that appears.

One way to write your code:

proc summary data=have nway;
    class estyr cname;
    var inf;
    output out=_stats_ sum=;
run;

[anner]

Thank you for this.  Is there a way to have the results show something like this?

I want to find out this, total of INF:

	Year
	2007	2008	2009	2010
County1	###	###	###	###
County2
County3

[PaigeMiller]

---

@anner wrote:

Thank you for this.  Is there a way to have the results show something like this?

I want to find out this, total of INF:

	Year
	2007	2008	2009	2010
County1	###	###	###	###
County2
County3

---

proc report data=have;
    columns cname estyr,inf;
    define cname/group 'County';
    define estyr/across 'Year';
    define inf/analysis sum;
run;

[anner]

Here is my current code that does not work like I want it to.

Data c10;
Infile 'Q:\POP\Estimates\Postcensal\page2010.raw' LRECL=540 missover;
InPUT @21 estyr 4. @29 cname $char12.    @46 tname $char21.
  @67 totpop 6. @73 Inf 6. @79 (p1-p37) (37*6.) @301 totfem 6.; 
RUN;
Data c09;
Infile 'Q:\POP\Estimates\Postcensal\page2009.raw' LRECL=540 missover;
InPUT @21 estyr 4. @29 cname $char12.    @46 tname $char21.
  @67 totpop 6. @73 Inf 6. @79 (p1-p37) (37*6.) @301 totfem 6.; 
RUN;
Data c08;
Infile 'Q:\POP\Estimates\Postcensal\page2008.raw' LRECL=540 missover;
InPUT @21 estyr 4. @29 cname $char12.   @46 tname $char21.
  @67 totpop 6. @73 Inf 6. @79 (p1-p37) (37*6.) @301 totfem 6.; 
RUN;
Data c07;
Infile 'Q:\POP\Estimates\Postcensal\page2007.raw' LRECL=540 missover;
InPUT @21 estyr 4. @29 cname $char12.    @46 tname $char21.
  @67 totpop 6. @73 Inf 6. @79 (p1-p37) (37*6.) @301 totfem 6.;
RUN;

Data all;
set c10 c09 c08 c07;
keep estyr cname inf;
run;

PROC SUMMARY DATA=all;
CLASS cname;
VAR inf estyr;
OUTPUT OUT=uno SUM=;
RUN;

PROC SORT data=uno;
by cname;
run;

proc print data=uno;
id cname;
VAR inf;
run;

[ballardw]

This may get you started:

proc tabulate data= all;
   class estyr   cname;
   var inf;
   table cname='',
         estyr='Year'* inf=''* sum=''
   ;
run;

The =' ' are to suppress default variable or statistic labeles as you did not show that you wanted them.

[anner]

Thank you ballardw, this worked well too.

[Shmuel · Accepted solution]

I'm not sure I use correctly your variables, but try next code:

proc tabulate data=all missing ;
   class cname estyr;     /* row and column categories */
   var inf;                        
   table cname,
          esttyr * (inf='<label>*f=<format>*sum=' ' ;
run;

[anner]

Thank you Shmuel, this worked perfectly and allowed me to format the output.