Hi,
I have a dataset with program ID, total number of participants, and participant count for demographic sub-categories such as age, gender, and ethnicity. Total participants should equal to the total for each sub-categories but this is not always the case.
I would like to do the following:
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
Data
data y.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.;
datalines4;
1,22,0,13,9,0,0,0,0,0,0,0,12,10,0,19,0,0,0,0,3
2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
4,6,0,0,0,5,1,0,0,0,0,0,2,4,0,5,0,0,0,1,0
5,0,3,2,0,0,1,0,2,0,0,10,4,4,10,5,0,0,0,13,0
6,0,2,1,0,0,4,0,1,1,0,0,5,4,0,7,2,0,0,0,0
7,0,0,0,0,0,2,5,10,5,0,0,10,12,0,21,1,0,0,0,0
8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
12,2,0,0,0,0,0,0,0,2,0,0,0,0,2,2,0,0,0,0,0
13,25,0,0,5,20,0,0,0,0,0,0,5,20,0,22,0,0,0,3,0
14,24,0,0,5,19,0,0,0,0,0,0,12,12,0,23,0,0,0,1,0
15,0,34,54,17,5,0,10,40,20,0,0,90,90,0,170,10,0,0,0,0
16,0,0,5,20,40,10,10,30,30,10,0,75,80,0,150,5,0,0,0,0
17,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
18,12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
19,9,0,0,9,0,0,0,0,0,0,0,0,9,0,6,3,0,0,0,0
20,150,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
;;;;
I am using SAS 9.4.
Thank you!
I don't think it is worth the bother of the comparison to match if you have a rule to reassign as stated. If the comparison is OK then the max of the sum of each group yields the same result. Note nesting of SUM within the MAX function makes this fairly easy.
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.; new_totalParticipants = max( sum(of age:), sum(Male, Female, GenderUnk ), sum( NotHispanic, MexicanChicano, PuertoRican, Cuban, OtherHisp, HispUnk)); datalines4; 1,22,0,13,9,0,0,0,0,0,0,0,12,10,0,19,0,0,0,0,3 2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 4,6,0,0,0,5,1,0,0,0,0,0,2,4,0,5,0,0,0,1,0 5,0,3,2,0,0,1,0,2,0,0,10,4,4,10,5,0,0,0,13,0 6,0,2,1,0,0,4,0,1,1,0,0,5,4,0,7,2,0,0,0,0 7,0,0,0,0,0,2,5,10,5,0,0,10,12,0,21,1,0,0,0,0 8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 12,2,0,0,0,0,0,0,0,2,0,0,0,0,2,2,0,0,0,0,0 13,25,0,0,5,20,0,0,0,0,0,0,5,20,0,22,0,0,0,3,0 14,24,0,0,5,19,0,0,0,0,0,0,12,12,0,23,0,0,0,1,0 15,0,34,54,17,5,0,10,40,20,0,0,90,90,0,170,10,0,0,0,0 16,0,0,5,20,40,10,10,30,30,10,0,75,80,0,150,5,0,0,0,0 17,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 18,12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 19,9,0,0,9,0,0,0,0,0,0,0,0,9,0,6,3,0,0,0,0 20,150,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ;;;;
I don't think it is worth the bother of the comparison to match if you have a rule to reassign as stated. If the comparison is OK then the max of the sum of each group yields the same result. Note nesting of SUM within the MAX function makes this fairly easy.
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.; new_totalParticipants = max( sum(of age:), sum(Male, Female, GenderUnk ), sum( NotHispanic, MexicanChicano, PuertoRican, Cuban, OtherHisp, HispUnk)); datalines4; 1,22,0,13,9,0,0,0,0,0,0,0,12,10,0,19,0,0,0,0,3 2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 4,6,0,0,0,5,1,0,0,0,0,0,2,4,0,5,0,0,0,1,0 5,0,3,2,0,0,1,0,2,0,0,10,4,4,10,5,0,0,0,13,0 6,0,2,1,0,0,4,0,1,1,0,0,5,4,0,7,2,0,0,0,0 7,0,0,0,0,0,2,5,10,5,0,0,10,12,0,21,1,0,0,0,0 8,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 9,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 12,2,0,0,0,0,0,0,0,2,0,0,0,0,2,2,0,0,0,0,0 13,25,0,0,5,20,0,0,0,0,0,0,5,20,0,22,0,0,0,3,0 14,24,0,0,5,19,0,0,0,0,0,0,12,12,0,23,0,0,0,1,0 15,0,34,54,17,5,0,10,40,20,0,0,90,90,0,170,10,0,0,0,0 16,0,0,5,20,40,10,10,30,30,10,0,75,80,0,150,5,0,0,0,0 17,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 18,12,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 19,9,0,0,9,0,0,0,0,0,0,0,0,9,0,6,3,0,0,0,0 20,150,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ;;;;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.