Hello,
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*/
proc sql;
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
from work.registry_pop_sum_uz
where urban_zone='METRO-CALGARY'
) as a
, (select sex_num, age_group1, ahs_pop as tot_sexage_ahs_pop
from work.registry_pop_sum_SexAge
where sex_num=0 and age_group1='18-34'
) as b;
quit;
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:
urban_zone, ahs_pop
metro-calgary, 978,418
metro-edmonton, 723,150
rural-calgary, 81,662,
etc.
