Update and replace data in a table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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 3 columns:

 

Date                    Calls Made                Calls Cancelled

05/01/2018             5000                              2000

06/01/2018             5800                              2300

07/01/2018             7600                              2100

08/01/2018             6600                              1000

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             3400                              2400

 

This programs runs once, every month end and inserts the new row into the table. The new requirement is that I need to run the program monday of every week and replace the last row with that day data until the end of the month when the row is recorded into the table as the month ending value. Next month the same is continued until the month end. 

 

What I mean is:

 

November- week1  (Monday)

Date                    Calls Made                Calls Cancelled

 

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             3400                              2400

 

November- week2  (Monday)

Date                    Calls Made                Calls Cancelled

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             4400                              2600

 

November- week3  (Monday)  

Date                    Calls Made                Calls Cancelled

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             5300                              3000

 

End of month - November 30, 2018 

Date                    Calls Made                Calls Cancelled

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             6000                              4000

 

Dec-week1 (Monday)

Date                    Calls Made                Calls Cancelled

09/01/2018             5600                              3400

10/01/2018             4400                              2500

11/01/2018             6000                              4000

12/01/2018             1000                               400

 

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


Accepted Solutions
Solution
2 weeks ago
Trusted Advisor
Posts: 1,345

Re: Update and replace data in a table

This is a good application for the MODIFY statement, in which observations are deleted, added, or replaced in place.  I.e. the whole dataset is not copied to a new version just to change a very few records.

 

The following code will take a single record from a transaction data set (i.e. your new data each Monday), and if it does not have the same date as the end of the master file, the record is appended.  But if it has the same date, then the last master record is merely re-written with the updated values:

 

data master;
  input date mmddyy10. Calls_Made  Calls_Cancelled;
  format date date9.;
datalines;
05/01/2018  5000  2000
06/01/2018  5800  2300
07/01/2018  7600  2100
08/01/2018  6600  1000
09/01/2018  5600  3400
10/01/2018  4400  2500
run;


data transaction;
  input date mmddyy10. calls_made calls_cancelled;
  format date date9.;
datalines;
11/01/2018  3400  2400
run;

data master;
  modify master nobs=nmaster point=nmaster;
  set transaction (rename=(date=tran_date));
  if date =tran_date then replace;
  else do;
    date=tran_date;
    output;
  end;
run;

 

 

 The first time you run the above, a November record is added.  If the following Monday, you make a new transaction dataset with DATE=11/01/2018 and new values for calls_made, calls_cancelled. and then run the last data step above, you'll replace the values for November.

 

Note the MODIFY statement reads only the last record in dataset master, no matter how many observations are in master.  Sequential reading is avoided.

 

View solution in original post


All Replies
Solution
2 weeks ago
Trusted Advisor
Posts: 1,345

Re: Update and replace data in a table

This is a good application for the MODIFY statement, in which observations are deleted, added, or replaced in place.  I.e. the whole dataset is not copied to a new version just to change a very few records.

 

The following code will take a single record from a transaction data set (i.e. your new data each Monday), and if it does not have the same date as the end of the master file, the record is appended.  But if it has the same date, then the last master record is merely re-written with the updated values:

 

data master;
  input date mmddyy10. Calls_Made  Calls_Cancelled;
  format date date9.;
datalines;
05/01/2018  5000  2000
06/01/2018  5800  2300
07/01/2018  7600  2100
08/01/2018  6600  1000
09/01/2018  5600  3400
10/01/2018  4400  2500
run;


data transaction;
  input date mmddyy10. calls_made calls_cancelled;
  format date date9.;
datalines;
11/01/2018  3400  2400
run;

data master;
  modify master nobs=nmaster point=nmaster;
  set transaction (rename=(date=tran_date));
  if date =tran_date then replace;
  else do;
    date=tran_date;
    output;
  end;
run;

 

 

 The first time you run the above, a November record is added.  If the following Monday, you make a new transaction dataset with DATE=11/01/2018 and new values for calls_made, calls_cancelled. and then run the last data step above, you'll replace the values for November.

 

Note the MODIFY statement reads only the last record in dataset master, no matter how many observations are in master.  Sequential reading is avoided.

 

Occasional Contributor
Posts: 17

Re: Update and replace data in a table

Hey, thank you very much for the reply and explaining the solution. It worked just fine, exactly like I needed.
Occasional Contributor
Posts: 17

Re: Update and replace data in a table

Hey, I have another question similar to the one you've helped me with. 

 

The situation is the same but the date are daily and I need to load and replace the data for the current month, once every week. 

 

Example: week 1 

     date              Total_calls

01JUL2018            20
02JUL2018            22
03JUL2018            23
04JUL2018            44
05JUL2018            55
06JUL2018            43
07JUL2018            21

 

Week 2:

date              Total_calls

01JUL2018            22
02JUL2018            24
03JUL2018            65
04JUL2018            80
05JUL2018            78
06JUL2018            49
07JUL2018            99

08JUL2018            20
09JUL2018            22
10JUL2018            23
11JUL2018            44
12JUL2018            55
13JUL2018            43

The reason for doing this is that the data keeps changing every day, so I need updated data every week when I run the code. 

Looking forward to hearing from you. Thank you very much !

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 164 views
  • 3 likes
  • 2 in conversation