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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: