Update and replace data in a table.

Reply
Occasional Contributor
Posts: 17

Update and replace data in a table.

Hello all,

 

I need some help with one of my programs. Any help is appreciated. Thanks in advance.

 

I have a data set called CALLS which consists of 2 columns:

 

Date                   Calls Made                

04/28/2018            5000                              

04/29/2018            5800                              

04/30/2018            7600                             

05/01/2018            6600                              

05/02/2018            5600                              

05/03/2018            4400  

05/04/2018            7600                             

05/05/2018            6600                              

05/06/2018            5600                              

05/07/2018            4400

                                                     

 

This program is scheduled to run every Monday for the past 10 days . The next Monday the program needs to run and replace all the values for the past 30 days only keeping the historical data (i.e)  of past dates that exist in the table. 

 

This needs to be done cause the "calls made" changes every day and I need to report the refreshed data every Monday keeping all the historical data.

 

I just need a logic that can be used to replace the past 30 days data from the main table every week with the refreshed data. 

 

 

What I mean is:

 

May- week1  (Monday)

Date                   Calls Made                

04/28/2018            5000                              

04/29/2018            5800                              

04/30/2018            7600                             

05/01/2018            6600                              

05/02/2018            5600                              

05/03/2018            4400  

05/04/2018            7600                             

05/05/2018            6600                              

05/06/2018            5600                              

05/07/2018            4400

 

May - week2  (Monday)

Date                   Calls Made                

05/04/2018            12000                              

05/05/2018            10000                             

05/06/2018            13200                           

05/07/2018            9000                           

05/08/2018            5600                              

05/09/2018            4400 

05/10/2018            5400                              

05/11/2018            5500                              

05/12/2018            7900                             

05/13/2018            8600                              

05/14/2018            7600                              

05/17/2018            8400 

 

and so on.. 

 

Any help will be greatly appreciated. Looking forward to it. Thank you very much. 

Occasional Contributor
Posts: 17

Re: Update and replace data in a table.

You just want to replace the last 10 days of data on your table?

 

data grab_this_week;

set table_you_are_pulling_from;

if intnx('day',today(),-10)<=call_date<=today() then output;

else delete;

run;

proc sort data=grab_this_week;

by call_date;

run;

proc sort data=full_data;

by call_date;

run;

data full_data;

merge full_data grab_this_week;

by call_date;

run;

 

what this will do it will keep the latest data. Whatever dataset you have on the far right will be the latest information. grab_this_week will overwrite those dates you have on full_data. The call_date that you don't have in grab_this_week will be kept in full_data.

 

Valued Guide
Posts: 580

Re: Update and replace data in a table.

"I just need a logic that can be used to replace the past 30 days data from the main table every week with the refreshed data. "

 

And what is the replacement for the past 30 days?

 

Posting the code currently building the main table would be useful.

 

If you mean "remove past 30 days and add new data" maybe something like the following untested code helps (assuming that "date" is a sas-date-variable):

data main_table;
  set main_table(where=(date >= today()-30)) 
    new_data;
run;

Making a backup of main_table before that step is recommended.

Ask a Question
Discussion stats
  • 2 replies
  • 66 views
  • 0 likes
  • 3 in conversation