BookmarkSubscribeRSS Feed
ka2yee
Calcite | Level 5

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.

2 REPLIES 2
Reeza
Super User

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.

ka2yee
Calcite | Level 5
Thanks very much for your suggestion. I'll have a look. Best,



Karen


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 802 views
  • 0 likes
  • 2 in conversation