topic proc tabulate in SAS Programming

proc tabulate

mary_mcneill — Fri, 29 Mar 2024 22:40:21 GMT

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!

Re: proc tabulate

PaigeMiller — Fri, 29 Mar 2024 22:53:40 GMT

You probably want to use the WEIGHT statement.

weight population;

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

Re: proc tabulate

mary_mcneill — Fri, 29 Mar 2024 22:59:53 GMT

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;

Re: proc tabulate

PaigeMiller — Fri, 29 Mar 2024 23:19:54 GMT

Does your code work?

Re: proc tabulate

mary_mcneill — Fri, 29 Mar 2024 23:41:05 GMT

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;

Re: proc tabulate

mary_mcneill — Fri, 29 Mar 2024 23:41:56 GMT

Re: proc tabulate

PaigeMiller — Fri, 29 Mar 2024 23:47:54 GMT

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?

Re: proc tabulate

mary_mcneill — Fri, 29 Mar 2024 23:53:33 GMT

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:

Re: proc tabulate

PaigeMiller — Sat, 30 Mar 2024 00:01:12 GMT

Instead of the weight statement, try

var pop;

Re: proc tabulate

ballardw — Sat, 30 Mar 2024 02:38:22 GMT

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.

Re: proc tabulate

mary_mcneill — Sat, 30 Mar 2024 17:25:51 GMT

same outcome.

Re: proc tabulate

PaigeMiller — Sat, 30 Mar 2024 18:43:44 GMT

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

Re: proc tabulate

mary_mcneill — Sat, 30 Mar 2024 18:55:49 GMT

This worked!

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

Here are the variable types:

Re: proc tabulate

ballardw — Sat, 30 Mar 2024 22:18:48 GMT

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