04-04-2017 05:50 PM
Could use some advice on how to tackle obtaining weights for multiple variable combinations. I was able to write the following code and have it work successfully:
/*weight for sex=female, age_group=18-34; urban_zone=metro-calgary*/
create table work.wt_by_sexAgeUz as
select b.sex_num, b.age_group1, a.urban_zone, (b.tot_sexage_ahs_pop/&tot_ahs_pop)*a.uz_ahs_pop format=comma12.3 as weight
from (select urban_zone, ahs_pop as uz_ahs_pop
) as a
, (select sex_num, age_group1, ahs_pop as tot_sexage_ahs_pop
where sex_num=0 and age_group1='18-34'
) as b;
but now what I'd like to do is to obtain results for various combinations. For example:
1) keep sex= female and urban_zone='METRO-CALGARY' but calculate weights for each of remaining age groups
2) keep sex=female and change urban_zone='METRO-EDMONTON' and calculate weights for each of the age groups
3) repeat 1 and 2 with sex=male
4) repeat 2 but change out the urban_zone with the other 6 categories I have
What I have is:
sex = male or female (2 categories total)
age_group = '18-34', '35-44', '45-54', etc. (8 categories total)
urban_zone = 'metro-calgary', 'metro-edmonton', etc. (20 categories total)
So there is a lot of combinations to obtain weights for. I thought about macros but don't know how to dyanmically change out the age category and urban_zone that is in quotations in the proc sql statements above. Appreciate any thoughts.
Sorry the attachement isn't working: here is the structure of the dataset for registry_pop_sum_uz:
04-04-2017 06:47 PM
We can't replicate your data without numbers but my suggestion would be to step outside of SQL and look first at PROC MEANS/SUMMARY to generate all the different combinations you'll need. Look at the WAYS and TYPES statements in PROC MEANS that allows you to control the levels of aggregation. Or you can use the CLASSDATA option to specifically specify the levels of interest.