Saving new table with update date

[PatrykSAS]

Hello,

I have a 2-part problem. Firstly, I want to keep historical information from the table while updating it. Second, I want to add the data from the penultimate table to the current dataset.

Regarding the first part, there is a table with id and score which can change every week/month. When updating the data, I want to save the table with the update date to the "ODSHMS" library. When data is refreshed twice on the same day, the table should overwrite the previous one with the same date. I would like to achieve the following effect.

TABLE updated 05.01.2021 is saved as TABLE_05012021 when updated 10.03.2021 saved as TABLE_10032021 etc.

Regarding the second part of the problem, I would like to add the columns from the table that was updated one before last to the data set. So if I have tables: TABLE_05012021, TABLE_14022021 and TABLE_10032021 then I want to refer to TABLE_14022021 and join columns with target dataset.

I am asking for your support and have a nice day 🙂

[Kurt_Bremser]

First, do not use the DMY order for such things. Use a YMD order instead, so the names sort nicely.

Second, this is the code:

data table_%sysfunc(today(),yymmddn8.);
set table;
run;

[PatrykSAS]

Thank you for your response. The case is that I need to extract the penultimate one from the saved tables, not the last one.

[Kurt_Bremser · Accepted solution]

When using an intelligent date order (that's why I said you DO NOT USE DMY dates), the last is the penultimate. Don't make your job unnecessarily hard by using stupid data structures.

Finding the last dataset in a series is then extremely easy:

proc sql noprint;
select max(memname) into :lastname
from dictionary.tables
where library = "LIBRARY" and memname like 'TABLE_%';
quit;