BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PatrykSAS
Obsidian | Level 7

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 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 406 views
  • 0 likes
  • 2 in conversation