proc tabulate

[mary_mcneill]

I have the following data

County	Year	Sex	Population
A	2013	Male	545131
A	2013	Female	64465
A	2014	Male	6543513
A	2014	Female	6546551
B	2013	Male	65651
B	2013	Female	6546541

I am using the following code:

proc tabulate data=work.census3 format=5.;
format sex $sex.;
class sex county year pop / missing preloadfmt;
tables county=''*sex='', year='' /printmiss box='Sex' rts=20;
keylabel n=' ' all='Total';
run;

This is only counting county by sex for each year and I want the population data for county by sex for each year. How do I modify the code to get the population data for each sex by year in the table? Pease help!

[PaigeMiller]

You probably want to use the WEIGHT statement.

weight population;

Also, I don't think population belongs in the CLASS statement.

[mary_mcneill]

So would you suggest this instead? Sorry I've never used the weight statement

proc tabulate data=work.census3 format=5.;
format sex $sex.;
class sex county year / missing preloadfmt;
weight pop;
tables county=''*sex='', year='' /printmiss box='Sex' rts=20;
keylabel n=' ' all='Total';
run;

[PaigeMiller]

Does your code work?

[mary_mcneill]

I tried this but it is now giving me the sum of the population for each year, but I want the individual populations for each sex. Any advice?

proc tabulate data=work.census3 format=5.;
format sex sex.;
class county year / missing preloadfmt;
var sex / weight=pop;
tables county=''*sex='', year='' /printmiss box='Sex' rts=20;
keylabel n=' ';
run;

[mary_mcneill]

[PaigeMiller]

Now you have changed the code so that SEX is not a variable in the CLASS statement. It doesn't seem to me, based on your most recent words, that you want to do this.

But I suggested two changes, and you haven't run that code; instead you made other changes that I didn't suggest and it doesn't work. What happens when you run the code with only the changes I suggested?

[mary_mcneill]

When I run this it does the same thing as before. 

proc tabulate data=work.census3 format=5.;
format sex sex.;
class sex county year / missing preloadfmt;
weight pop;
tables county=''*sex='', year='' /printmiss box='Sex' rts=20;
keylabel n=' ';
run;

Results:

[PaigeMiller]

Instead of the weight statement, try

var pop;

[mary_mcneill]

same outcome. 

[PaigeMiller]

Show us your code. We can't help you if we don't know what you did.

[ballardw · Accepted solution]

Is your variable named POP or POPULATION? Is that variable numeric or character.

This one reason that we suggest providing example data in the form of a working data step.

data have;
   input County $ Year $ Sex $ pop;
datalines;
A 	2013 	Male 	545131
A 	2013 	Female 	64465
A 	2014 	Male 	6543513
A 	2014 	Female 	6546551
B 	2013 	Male 	65651
B 	2013 	Female 	6546541
;

proc tabulate data=have format=best12. ;
   class sex county year  / ;
   var POP;
   tables county=''*(sex='' all), 
           year=''*pop=' '*sum /printmiss box='Sex' rts=20;
   keylabel n=' ' all='Total' sum=' ';
run;

Which yields:

Sex		2013	2014
A	Female	64465	6546551
	Male	545131	6543513
	Total	609596	13090064
B	Female	6546541	.
	Male	65651	.
	Total	6612192	.

You did not provide your Formats so we can't use them. So I removed all the preload format stuff as well.

Speaking of formats, you  are attempting to stuff values that start at 7 digits and then sum them resulting in 8 digit values into a 5 digit format.

[mary_mcneill]

This worked!

Yes, the variable name is pop, sorry I shortened it in my SAS program. 

Here are the variable types:

[ballardw]

---

@PaigeMiller wrote:

You probably want to use the WEIGHT statement.

weight population;

Also, I don't think population belongs in the CLASS statement.

---

Since there were no VAR variables there wasn't anything to apply the Weight to.

The FREQ statement would work allowing each observation to be counted POP times. A quick test shows that POP on the Class statement doesn't cause a problem if used as a FREQ variable, using the Have dataset in another post.

proc tabulate data=have format=best12. ;
   class sex county year pop / ;
   freq POP;
   tables county=''*(sex='' all), 
           year=''*n/printmiss box='Sex' rts=20;
   keylabel n=' ' all='Total' sum=' ';
run;