macro loop for multiple variable combination outputs

ka2yee — Tue, 04 Apr 2017 21:50:52 GMT

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.

Re: macro loop for multiple variable combination outputs

Reeza — Tue, 04 Apr 2017 22:47:11 GMT

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.

Re: macro loop for multiple variable combination outputs

ka2yee — Wed, 05 Apr 2017 14:31:57 GMT

Thanks very much for your suggestion. I'll have a look. Best,

Karen

topic Re: macro loop for multiple variable combination outputs in SAS Programming

macro loop for multiple variable combination outputs

Re: macro loop for multiple variable combination outputs

Re: macro loop for multiple variable combination outputs