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. ... View more

Hello,   I can't seem to get the piece of code identified below to work.  I'm trying to get the maximum # of distinct values for the age_group, urban, and urban_zone variables.  Use these numbers to subsequently run a do loop to store each of the disinct values into macros to retrieve later.  I'd like to do this dynamically.  Hope this makes sense.   Appreciate any help.   K.   /**************************************************************************************/ /*store values from macro loops for retrieve in later step to calculate weights & prevalence rates*/ /**************************************************************************************/ /*create global macros for the 3 variables of interest*/  %LET LBL1=age_group;  %LET LBL2=urban;  %LET LBL3=urban_zone; /*obtain list of distinct categories for each of the 3 variables of interest*/ /*obtain the total number of distinct categories for each of the 3 variables of interest*/ /*obtain the maximum number of distinct categories for each of the 3 variables of interest*/ %macro var_for_val(dataset,var);     proc sql noprint;         create table dist_var_&&LBL&k as         select distinct &var         from &dataset;     quit;     proc sql noprint;         create table dist_var2_&&LBL&k as         select monotonic() as id, &var         from dist_var_&&LBL&k;     quit;    %global max_var_&&LBL&k;  proc sql noprint;    select distinct max(id)    into :max_var_&&LBL&k   from dist_var2_&&LBL&k;  quit;     /*CANNOT GET THIS PIECE OF CODE TO WORK - seems not to recognize the value but instead the macro name*/  %do i = 1 %to &max_var_&&LBL&k;         %global var_&&LBL&k_&i;         proc sql noprint;             select distinct &var             into :var_&&LBL&k_&i             from dist_var2_&&LBL&k             where id = &i;         quit;         %put &&var_&&LBL&k_&i;     %end; /*THIS PIECE OF CODE NOT WORKING END*/   %mend var_for_val; %macro geo_var;  %do k=1 %to 3;   %var_for_val (pop,var=&&LBL&k);  %end; %mend geo_var;   /*run the geo_var macro to get the distinct values for each of the variables of interest*/ %geo_var; /*display all the macro values*/ %put _ALL_; ... View more