How to pull the base data records from 12 separately monthly file

[BaalaRaaji]

Hi there,

I have a base data example (9k records for 2023 (Jan-Dec)).

For these 9k records only i have to join the previous monthly datasets which are individually saved ex: acctJan22, acctFeb22,.....acctDec23 (24 months data).

Am looking to create a loop to get few columns from each datasets for last 12 months .

ex: 9k records (split by month- its has data from Jan23-Dec23)- For this dataset i have to join previous 12 months data to check their performance.

like example say i have 500records for jan'23- I need to get there previous 12 month balance and buckets ..that would be from Jan22 to Jan23, For Feb'23 from base data i have to look back from Feb'22 to Feb'23...like vice versa.

i don't have sample data but hope its clear.. 

Could anyone share any macro loop logic. Thanks

Regards,

rs

[LinusH]

Macro loop seem unnecassery (based on the information given.

I would just append the base datasets and then do the join:

data allmonths;
   set acctjan23 acctfeb23 etc....
run;

[BaalaRaaji]

Each monthly table has 5million records so need to run in loop for 12/24 months

[Kurt_Bremser]

---

@BaalaRaaji wrote:
Each monthly table has 5million records so need to run in loop for 12/24 months

---

Why? 120 M observations are easy to handle with SAS, unless you have very large variables.

[Kurt_Bremser]

And if the individual datasets are sorted by the key, you can kepp the sort order like this:

data acct_all / view=act_all;
set
  acctjan22
  acctfeb22
  ...
;
by /* key variable(s) */;
run;

The view needs next to no place, and you can use it in the join. If you want to keep information from where an observation came, use the INDSNAME= option when defining the view.

[Kurt_Bremser]

And you have to SERIOUSLY rethink your dataset naming method. Timestamps in filenames should always be in YMDHMS order, with 4-digit years, and everything as numbers with leading zeroes where needed. This makes handling such files in chronological order and with wildcards much easier.

[Sarath_A_SAS]

You can create a macro loop in SAS that dynamically selects the relevant previous 12-month datasets based on the month in the base dataset. Here's an example that should guide you in creating the logic:

Assumptions:

• You have monthly datasets named acctJan22, acctFeb22, ..., acctDec23.
• You want to join these datasets with the current month’s base data to get columns like balance and buckets for each of the previous 12 months.

Explanation:

• base_data: The dataset with your 9k records for 2023.
• month_list: Extracts distinct months from the base dataset.
• Looping: For each month in the base dataset, it calculates the previous 12 months and joins the relevant datasets.
• Join Logic: For each of the 12 previous months, it left joins the corresponding dataset (acctJan22, etc.) and appends the balance and bucket information.

You can adjust this further based on the specific column names and structures of your datasets. This should help you automate the process of joining the last 12 months' data for each record in your base data.

%macro join_previous_months(base_data=, start_year=2022, start_month=1, num_months=12);
    /* Loop through each record in your base data */
    proc sql noprint;
        select distinct put(year_month, yymmn6.) into :month_list separated by ' '
        from &base_data;
    quit;

    %let total_months = %sysfunc(countw(&month_list));

    /* Loop through each month in the base data */
    %do i = 1 %to &total_months;
        %let current_month = %scan(&month_list, &i);

        /* Extract year and month */
        %let current_year = %substr(&current_month, 1, 4);
        %let month_num = %substr(&current_month, 5, 2);

        /* Create temporary datasets for previous 12 months */
        data temp_&current_month;
            set &base_data(where=(year_month = "&current_month"));

            %do m = 1 %to &num_months;
                /* Calculate month and year for the previous months */
                %let prev_month = %eval(&month_num - &m);
                %if &prev_month <= 0 %then %do;
                    %let prev_year = %eval(&current_year - 1);
                    %let prev_month = %eval(12 + &prev_month);
                %end;
                %else %do;
                    %let prev_year = &current_year;
                %end;

                /* Zero-pad month */
                %let prev_month = %sysfunc(putn(&prev_month, z2.));

                /* Join previous month's data */
                proc sql;
                    create table merged_&current_month as
                    select a.*, b.balance as balance_&m, b.buckets as buckets_&m
                    from temp_&current_month as a
                    left join acct&prev_month.&prev_year as b
                    on a.acct_id = b.acct_id;
                quit;

                /* Set temp_&current_month to the newly merged dataset */
                data temp_&current_month;
                    set merged_&current_month;
                run;
            %end;
        run;
    %end;

    /* Merge all months into a final dataset */
    data final_output;
        set temp_:;
    run;

%mend join_previous_months;

/* Example Usage */
%join_previous_months(base_data=base_data_2023, start_year=2022, start_month=1, num_months=12);