BookmarkSubscribeRSS Feed
ndee
Calcite | Level 5

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. 

2 REPLIES 2
EEng
Obsidian | Level 7

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.

 

andreas_lds
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2 replies
  • 315 views
  • 0 likes
  • 3 in conversation