Merge multiple tables in sas using loop or macro

Question

I have generated the tables of Forecast_2013 to 2022 in a loop and then merged all datasets in to 1 Table. But now I want to do merge the datasets in a loop with irrespective of years, The next year will be 2023 or 2024...I dont want to do mannually to set Forecast_2023;set forecast_2024. How can I put in to loop using macro?

Data P_OT.FORECAST(DROP=td qq AGE1 AGE2 AGE3 AGE4  AGEBANDFCST020 AGEBANDFCST030 AGEBANDFCST035P
     HSI1_2012 HSI1_2013 HSI1_2014 HSI1_2015 HSI1_2016 HSI1_2017 HSI1_2018 HSI1_2019 HSI1_2020  HSI1_2021 HSI1_2022);

set FORECAST_2013;set FORECAST_2014;set FORECAST_2015;set FORECAST_2016;
set FORECAST_2017;set FORECAST_2018;set FORECAST_2019;set FORECAST_2020;
set FORECAST_2021;set FORECAST_2022;
run;

Answer 1

An alternative to what Scott posted would be:

*Assign library to folder where FORECAST_ files are located;
libname NAME 'C:\Path to Folder';

*Data step to stack files;
Data P_OT.FORECAST(DROP=td qq AGE1 AGE2 AGE3 AGE4  AGEBANDFCST020 
    AGEBANDFCST030 AGEBANDFCST035P HSI1_:);
    set NAME.FORECAST_:;
run;

This should give the same results as what Scott posted using name prefix lists instead of using SQL to produce lists of datasets to be merged and variables to be dropped.

The code above will stack all datasets in the libname library that start with "FORECAST_". It will also drop all variables in the created dataset that begin with "HSI1_".

Answer 2

You can use proc sql and the sashelp.vcolumn table to find the names of all your tables and create a macro variable containing them. To do this all your tables need to be in the same library, and any other tables in the library can not contain FORCAST_. The part of the sql code that says memname like '%FORECAST_%' is doing a search on all the tables in the library called libname to select the tables that contain FORCAST_. The sql step creates a list of your tables that you can then inject into your data step to stack them.

Again: Be careful there are not other tables with the name like FORECAST_ or it will try to stack tables you do not want. The easiest way to ensure this would be to put them in their own library when you create these tables.

If you have all these tables in the work library then replace libname with work

I'm on my phone and haven't checked the substr and index part, but if i recall correctly that should work.

proc sql noprint;

    select "libname."||memname
    into :stack_tables separated by ' '
    from sashelp.vcolumn
    where libname = upper("libname")
        and
            memname like '%FORECAST_%'
    ;

    select "HSI1_"||substr(memname,index(memname,"_")+1,4)
    into :drop_vars separated by ' '
    from sashelp.vcolumn
    where libname = upper("libname")
        and
            memname like '%FORECAST_%'
    ;
quit;

Data P_OT.FORECAST(DROP=td qq AGE1 AGE2 AGE3 AGE4  AGEBANDFCST020 AGEBANDFCST030 AGEBANDFCST035P
     &drop_vars.);
    set &stack_tables.;
run;