topic Re: Update and replace data in a table. in SAS Programming

Update and replace data in a table.

ndee — Wed, 11 Jul 2018 16:54:37 GMT

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.

Re: Update and replace data in a table.

EEng — Thu, 12 Jul 2018 18:29:01 GMT

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.

Re: Update and replace data in a table.

andreas_lds — Fri, 13 Jul 2018 04:46:54 GMT

"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.