topic Re: How do I sum values by year in SAS Programming

How do I sum values by year

anner — Tue, 20 Aug 2019 18:45:49 GMT

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

Re: How do I sum values by year

PaigeMiller — Tue, 20 Aug 2019 18:50:19 GMT

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;

Re: How do I sum values by year

anner — Tue, 20 Aug 2019 18:58:42 GMT

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;

Re: How do I sum values by year

anner — Tue, 20 Aug 2019 19:03:37 GMT

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

Re: How do I sum values by year

ballardw — Tue, 20 Aug 2019 19:23:39 GMT

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.

Re: How do I sum values by year

Shmuel — Tue, 20 Aug 2019 19:30:22 GMT

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;

Re: How do I sum values by year

PaigeMiller — Tue, 20 Aug 2019 20:12:51 GMT

@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;

Re: How do I sum values by year

anner — Wed, 21 Aug 2019 10:28:39 GMT

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

Re: How do I sum values by year

anner — Wed, 21 Aug 2019 10:30:35 GMT

Thank you ballardw, this worked well too.