如何编写SAS宏从指定库中选取最新的data_mtly格式数据集

Let's build a robust SAS macro to solve your problem—finding the most recent data_mtly_xxxxxx dataset in your mylib library. Here's a step-by-step solution tailored to your dataset naming convention:

Macro Code #

%macro get_latest_mtly(lib=mylib, out_ds=latest_mtly_data);
    /* Extract all monthly datasets and convert their name suffix to SAS dates */
    proc sql noprint;
        select memname, 
               input(substr(memname, 11), date9.) as dataset_date format=date9.
        into :mtly_datasets separated by ' ',
             :mtly_dates separated by ' '
        from dictionary.tables
        where libname = upcase("&lib")
              and memname like 'DATA_MTLY_%'
        order by calculated dataset_date desc;
    quit;

    /* Handle case where no monthly datasets exist */
    %if &sqlobs = 0 %then %do;
        %put ERROR: No datasets matching the data_mtly_xxxxxx pattern found in library &lib.;
        %return;
    %end;

    /* Grab the first (most recent) dataset from the sorted list */
    %let latest_dataset = %scan(&mtly_datasets, 1);

    /* Create a copy of the latest dataset (skip this if you only need a reference) */
    data &out_ds;
        set &lib..&latest_dataset;
    run;

    %put NOTE: Successfully loaded the latest monthly dataset: &lib..&latest_dataset;
%mend get_latest_mtly;

/* Example usage */
%get_latest_mtly(lib=mylib, out_ds=my_latest_monthly_data);

How It Works #

• Dataset Discovery: We leverage dictionary.tables (SAS's built-in metadata table) to filter all datasets in your target library that start with DATA_MTLY_.
• Date Extraction: substr(memname, 11) pulls the date suffix from the dataset name (since data_mtly_ is exactly 10 characters long), then input(..., date9.) converts that string (like 01JUN2015) into a SAS date value for easy chronological comparison.
• Sort & Select: We sort datasets by their extracted date in descending order, so the first entry in our list is the most recent one.
• Error Handling: If no matching datasets exist, the macro throws a clear error message and exits early to avoid downstream issues.
• Flexible Output: The macro creates a copy of the latest dataset with your specified output name, or you can directly reference &lib..&latest_dataset in subsequent code instead of making a copy.

Key Notes #

• Double-check your dataset date suffixes follow the DDMONYYYY format (e.g., 01DEC2017). If your naming convention uses a different date format, adjust the date9. argument in the input() function to match (e.g., mmddyy10. for 12/01/2017).
• Library names are converted to uppercase to match the case stored in dictionary.tables, ensuring consistent matching regardless of how you pass the library name to the macro.

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