Calculating demographic proportions

[Angi]

Hello,

I have a program implementation dataset with program ID, cohort number to which the participants belong, total number of participants, and participant count for demographic sub-categories such as age, gender, and ethnicity.

Variables for sub-category Age = Age04 Age511 Age1214 Age1517 Age1820 Age2124 Age2544 Age4564 Age65Plus AgeUnk

Variables for sub-category Gender = Male Female GenderUnk

Variables for sub-category Ethnicity = NotHispanic MexicanChicano PuertoRican Cuban OtherHisp HispUnk

I am trying to calculate demographic proportions by cohort. For example, in the gender category, I'd like to calculate the total number and proportion of males, females, and unknown for cohort 1, cohort 2, cohort 3. I'd appreciate any advice on how to do this.

data WORK.PARTICIPANTS(label='participants dataset written by Stat/Transfer Ver. 11.2.2106.0521       ');

infile datalines dsd truncover;
  input ProgramID_:32. TotalParticipants:32. Age04:32. Age511:32. Age1214:32. Age1517:32. Age1820:32. Age2124:32. Age2544:32. Age4564:32. Age65Plus:32. AgeUnk:32. Male:32. Female:32. GenderUnk:32. NotHispanic:32. MexicanChicano:32. PuertoRican:32. Cuban:32. OtherHisp:32. HispUnk:32. cohort:32.;
datalines4;
 
1 22 0 13 9 0 0 0 0 0 0 0 12 10 0 19 0 0 0 0 3 1
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
4 6 0 0 0 5 1 0 0 0 0 0 2 4 0 5 0 0 0 1 0 1
5 0 3 2 0 0 1 0 2 0 0 10 4 4 10 5 0 0 0 13 0 2
6 0 2 1 0 0 4 0 1 1 0 0 5 4 0 7 2 0 0 0 0 3
7 0 0 0 0 0 2 5 10 5 0 0 10 12 0 21 1 0 0 0 0 3
8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
12 2 0 0 0 0 0 0 0 2 0 0 0 0 2 2 0 0 0 0 0 2
13 25 0 0 5 20 0 0 0 0 0 0 5 20 0 22 0 0 0 3 0 2
14 24 0 0 5 19 0 0 0 0 0 0 12 12 0 23 0 0 0 1 0 2
15 0 34 54 17 5 0 10 40 20 0 0 90 90 0 170 10 0 0 0 0 2
16 0 0 5 20 40 10 10 30 30 10 0 75 80 0 150 5 0 0 0 0 1
17 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
18 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
19 9 0 0 9 0 0 0 0 0 0 0 0 9 0 6 3 0 0 0 0 1
20 150 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
;;;;

I am using SAS 9.4.

Thank you,

[Reeza]

Change your data structure and then you can use PROC FREQ. 

Create a single variable for age, sex, gender etc and then PROC FREQ will give you the Ns and Percents. 

---

@Angi wrote:

Hello,

I have a program implementation dataset with program ID, cohort number to which the participants belong, total number of participants, and participant count for demographic sub-categories such as age, gender, and ethnicity.

Variables for sub-category Age = Age04 Age511 Age1214 Age1517 Age1820 Age2124 Age2544 Age4564 Age65Plus AgeUnk

Variables for sub-category Gender = Male Female GenderUnk

Variables for sub-category Ethnicity = NotHispanic MexicanChicano PuertoRican Cuban OtherHisp HispUnk

I am trying to calculate demographic proportions by cohort. For example, in the gender category, I'd like to calculate the total number and proportion of males, females, and unknown for cohort 1, cohort 2, cohort 3. I'd appreciate any advice on how to do this.

input ProgramID_:32. TotalParticipants:32. Age04:32. Age511:32. Age1214:32. Age1517:32. Age1820:32. Age2124:32. Age2544:32. Age4564:32. Age65Plus:32. AgeUnk:32. Male:32. Female:32. GenderUnk:32. NotHispanic:32. MexicanChicano:32. PuertoRican:32. Cuban:32.

OtherHisp:32. HispUnk:32. cohort:32.;

datalines;

1 22 0 13 9 0 0 0 0 0 0 0 12 10 0 19 0 0 0 0 3 1

2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3

4 6 0 0 0 5 1 0 0 0 0 0 2 4 0 5 0 0 0 1 0 1

5 0 3 2 0 0 1 0 2 0 0 10 4 4 10 5 0 0 0 13 0 2

6 0 2 1 0 0 4 0 1 1 0 0 5 4 0 7 2 0 0 0 0 3

7 0 0 0 0 0 2 5 10 5 0 0 10 12 0 21 1 0 0 0 0 3

8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3

9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3

10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3

11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

12 2 0 0 0 0 0 0 0 2 0 0 0 0 2 2 0 0 0 0 0 2

13 25 0 0 5 20 0 0 0 0 0 0 5 20 0 22 0 0 0 3 0 2

14 24 0 0 5 19 0 0 0 0 0 0 12 12 0 23 0 0 0 1 0 2

15 0 34 54 17 5 0 10 40 20 0 0 90 90 0 170 10 0 0 0 0 2

16 0 0 5 20 40 10 10 30 30 10 0 75 80 0 150 5 0 0 0 0 1

17 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

18 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

19 9 0 0 9 0 0 0 0 0 0 0 0 9 0 6 3 0 0 0 0 1

20 150 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

;;;;

I am using SAS 9.4.

Thank you,

---

[Angi]

Thank you very much for your advice!

[PGStats · Accepted solution]

I would use a datastep to do the transpose and group the categories by sub-category. Proc freq will do the rest:

data PARTICIPANTS(
    label='participants dataset written by Stat/Transfer Ver. 11.2.2106.0521       ');

infile datalines truncover;
input ProgramID:32. TotalParticipants:32. Age04:32. Age511:32. Age1214:32. Age1517:32. 
    Age1820:32. Age2124:32. Age2544:32. Age4564:32. Age65Plus:32. AgeUnk:32. Male:32. 
    Female:32. GenderUnk:32. NotHispanic:32. MexicanChicano:32. PuertoRican:32. Cuban:32. 
    OtherHisp:32. HispUnk:32. cohort:32.;
datalines;
1 22 0 13 9 0 0 0 0 0 0 0 12 10 0 19 0 0 0 0 3 1
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
4 6 0 0 0 5 1 0 0 0 0 0 2 4 0 5 0 0 0 1 0 1
5 0 3 2 0 0 1 0 2 0 0 10 4 4 10 5 0 0 0 13 0 2
6 0 2 1 0 0 4 0 1 1 0 0 5 4 0 7 2 0 0 0 0 3
7 0 0 0 0 0 2 5 10 5 0 0 10 12 0 21 1 0 0 0 0 3
8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
12 2 0 0 0 0 0 0 0 2 0 0 0 0 2 2 0 0 0 0 0 2
13 25 0 0 5 20 0 0 0 0 0 0 5 20 0 22 0 0 0 3 0 2
14 24 0 0 5 19 0 0 0 0 0 0 12 12 0 23 0 0 0 1 0 2
15 0 34 54 17 5 0 10 40 20 0 0 90 90 0 170 10 0 0 0 0 2
16 0 0 5 20 40 10 10 30 30 10 0 75 80 0 150 5 0 0 0 0 1
17 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
18 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
19 9 0 0 9 0 0 0 0 0 0 0 0 9 0 6 3 0 0 0 0 1
20 150 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
;

data long;
length subcat cat $16;
set participants;

array a age:;
subcat="Age";
do i = 1 to dim(a);
    cat = vname(a{i});
    count = a{i};
    output;
    end;

array g  Male Female GenderUnk;    
subcat="Gender";
do i = 1 to dim(g);
    cat = vname(g{i});
    count = g{i};
    output;
    end;

array e NotHispanic MexicanChicano PuertoRican Cuban OtherHisp HispUnk;
subcat="Ethnicity";
do i = 1 to dim(e);
    cat = vname(e{i});
    count = e{i};
    output;
    end;

keep programID subcat cat count cohort;
run;

proc sort data=long; by subcat; run;

proc freq data=long;
by subcat;
weight count;
table cohort*cat;
run;

[Angi]

Thank you very much for the syntax for transposing and grouping the variables!