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

Update and replace data in a table

ndee — Fri, 06 Jul 2018 16:48:11 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 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 !

Re: Update and replace data in a table

mkeintz — Fri, 06 Jul 2018 17:45:09 GMT

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.

Re: Update and replace data in a table

ndee — Fri, 06 Jul 2018 22:50:14 GMT

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

Re: Update and replace data in a table

ndee — Mon, 09 Jul 2018 23:29:49 GMT

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 !