关于使用SAS Proc Report按国家生成人口统计表及宏变量实现的问询

Hey Sam, great questions—let’s break this down step by step to get your country-level population stats table up and running smoothly.

1. Using the BY Statement to Generate Tables with Dynamic First Column Names #

Absolutely, you can use SAS's BY statement to group your data by country, and dynamically update the first column’s header to match the current country. The key is combining BY group processing with macro variables to inject the country name into your report’s column label. Here’s a practical example using PROC REPORT (works similarly with PROC TABULATE):

First, let’s assume your source dataset has country names and population metrics (age groups, gender ratios, etc.):

/* Step 1: Filter and prep your data (we’ll link this to the macro list later) */
data filtered_pop;
    set your_source_dataset;
    where country in (&country_list.); /* Macro list we’ll generate next */
run;

/* Step 2: Capture the current country name for dynamic labeling */
%let current_country = ;

data _null_;
    set filtered_pop;
    by country;
    if first.country then do;
        /* Store the current country in a global macro variable */
        call symputx('current_country', country, 'G');
    end;
run;

/* Step 3: Generate the table with dynamic first column name */
proc report data=filtered_pop nowindows headline headskip;
    by country;
    column demo_metric age_distribution gender_ratio;
    
    /* Dynamically set the first column’s header to the current country */
    define demo_metric / display "&current_country. Population Metrics";
    define age_distribution / display "Age Group Breakdown";
    define gender_ratio / display "Male/Female Ratio";
    
    /* Optional: Add a title that includes the country */
    title1 "&current_country. Population Statistics";
run;

If you need separate tables for each country (saved as individual files), wrap this in an ODS loop using the macro list we’ll generate—each iteration will update current_country and output a unique table.

2. Generating a Macro Variable List of 15–25 Countries (With IF Condition Filters) #

To create a macro list of countries that meet your specific IF criteria, you can use either PROC SQL or a DATA step. The goal is to extract distinct country names that pass your filters, then store them in a space-separated macro variable. Here are two reliable methods:

Method 1: PROC SQL (Simpler for Most Cases) #

This method lets you directly query and filter countries, then cap the list at 25 entries (or ensure at least 15):

/* First, count how many countries meet your criteria */
proc sql noprint;
    select count(distinct country) into :country_count
    from your_source_dataset
    where /* Insert your IF conditions here—e.g., population > 10000000, region = 'Europe' */;
quit;

/* Generate the macro list based on the count */
%if &country_count. >= 15 and &country_count. <= 25 %then %do;
    proc sql noprint;
        select distinct country into :country_list separated by ' '
        from your_source_dataset
        where /* Same IF conditions as above */;
    quit;
%end;
%else %if &country_count. > 25 %then %do;
    /* If too many countries, sort by a metric (e.g., population) and take top 25 */
    proc sql noprint;
        select distinct country into :country_list separated by ' '
        from your_source_dataset
        where /* Same IF conditions */
        order by population desc
        outobs=25;
    quit;
    %put NOTE: Trimmed country list to top 25 (original count: &country_count.);
%end;
%else %do;
    %put ERROR: Only &country_count. countries meet your criteria—need at least 15. Adjust filters!;
%end;

/* Verify the macro variable */
%put Final country list: &country_list.;

Method 2: DATA Step (For More Complex Logic) #

If your IF conditions involve row-level processing that’s hard to write in SQL, use a DATA step to build the list:

data _null_;
    length country_str $2000; /* Make this long enough for all country names */
    retain country_str '';
    set your_source_dataset(keep=country where=(/* Your IF conditions here */)) end=eof;
    by country;
    
    if first.country then do;
        /* Append each unique country to the list */
        country_str = catx(' ', country_str, country);
    end;
    
    if eof then do;
        call symputx('country_list', country_str, 'G');
        call symputx('country_count', _n_, 'G'); /* Store total count */
    end;
run;

/* Same validation check as before */
%if &country_count. <15 or &country_count. >25 %then %do;
    %put WARNING: Country count is &country_count. (needs to be 15–25).;
%end;

Pro Tip: Handle Country Names with Spaces #

If any country names have spaces (e.g., "United States"), modify the separated by clause to use quotes:

select distinct country into :country_list separated by '","'

Then reference the list as "&country_list." in your WHERE clause to avoid errors.

Hope these solutions fit your needs—feel free to tweak the code to match your exact dataset structure or filter rules!

内容的提问来源于stack exchange，提问作者Sam