Fill transaction data from previous date(s)

[NikosStratis]

Hello team!

I need your support on filling data for all calendar dates, with previous day(s) data, in case there are no transactions for the specific calendar dates.

I have the following data:

data work.have_data;

infile datalines delimiter=','; 

Length EB_Key $25;

input  Cal_Date   :date9.   Tr_Date  :date9.   EB_Key $  Num01 4.;

datalines;

'19MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,1

'19MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,1

'19MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,3

'19MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,2

'19MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,7

'19MAR2021'd,'19MAR2021'd,11948572_032_Y_N,9

'19MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,11

'20MAR2021'd,.,.,,.

'21MAR2021'd,.,.,,.

'22MAR2021'd,'22MAR2021'd,11994045_YMZ5501_Y_N,2

'22MAR2021'd,'22MAR2021'd,11980670_IKN8318_Y_N,1

'22MAR2021'd,'22MAR2021'd,11955207_EPN8700_Y_N,1

'22MAR2021'd,'22MAR2021'd,11961477_NIZ3630_Y_N,3

'22MAR2021'd,'22MAR2021'd,11974312_ATB3768_Y_N,5

'22MAR2021'd,'22MAR2021'd,11948572_032_Y_N,8

'22MAR2021'd,'22MAR2021'd,11987246_HAN8863_Y_N,13

;

run;

First is the Calendar Date and second is the Transaction Date for the calendar dates 20Mar2021 and 21Mar2021 there are no transactions, so I need to fill both Calendar Dates with all Transaction from the immediate previous Transaction Date 19Mar2021.

My needed output shall look like that:

'19MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,1

'19MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,1

'19MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,3

'19MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,2

'19MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,7

'19MAR2021'd,'19MAR2021'd,11948572_032_Y_N,9

'19MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,11

/** Block for Calendar 20 Mar 2021  **/

'20MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,1

'20MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,1

'20MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,3

'20MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,2

'20MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,7

'20MAR2021'd,'19MAR2021'd,11948572_032_Y_N,9

'20MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,11

/** Block for Calendar 21 Mar 2021  **/

'21MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,1

'21MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,1

'21MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,3

'21MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,2

'21MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,7

'21MAR2021'd,'19MAR2021'd,11948572_032_Y_N,9

'21MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,11

/** Block for Calendar 21 Mar 2021 Remains As Is **/

'22MAR2021'd,'22MAR2021'd,11994045_YMZ5501_Y_N,2

'22MAR2021'd,'22MAR2021'd,11980670_IKN8318_Y_N,1

'22MAR2021'd,'22MAR2021'd,11955207_EPN8700_Y_N,1

'22MAR2021'd,'22MAR2021'd,11961477_NIZ3630_Y_N,3

'22MAR2021'd,'22MAR2021'd,11974312_ATB3768_Y_N,5

'22MAR2021'd,'22MAR2021'd,11948572_032_Y_N,8

'22MAR2021'd,'22MAR2021'd,11987246_HAN8863_Y_N,13

I attempted to do it using Lag but I was not successful with my logic.

Any help is welcomed!

Thank you in advance.

[Kurt_Bremser]

I guess you want to do a "look-ahead":

data work.have_data;
infile datalines delimiter=','; 
Length EB_Key $25;
format cal_date tr_date yymmdd10.;
input  Cal_Date   :date9.   Tr_Date  :date9.   EB_Key $  Num01 :4.;
datalines;
'19MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,1
'19MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,1
'19MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,3
'19MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,2
'19MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,7
'19MAR2021'd,'19MAR2021'd,11948572_032_Y_N,9
'19MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,11
'20MAR2021'd,.,.,,.
'21MAR2021'd,.,.,,.
'22MAR2021'd,'22MAR2021'd,11994045_YMZ5501_Y_N,2
'22MAR2021'd,'22MAR2021'd,11980670_IKN8318_Y_N,1
'22MAR2021'd,'22MAR2021'd,11955207_EPN8700_Y_N,1
'22MAR2021'd,'22MAR2021'd,11961477_NIZ3630_Y_N,3
'22MAR2021'd,'22MAR2021'd,11974312_ATB3768_Y_N,5
'22MAR2021'd,'22MAR2021'd,11948572_032_Y_N,8
'22MAR2021'd,'22MAR2021'd,11987246_HAN8863_Y_N,13
;

proc sort data=have_data (where=(eb_key ne ""));
by eb_key cal_date;
run;

data want;
merge
  have_data
  have_data (
    firstobs=2
    keep=cal_date eb_key
    rename=(cal_date=_cal_date eb_key=_eb_key)
  )
;
output;
if eb_key = _eb_key
then do cal_date = cal_date + 1 to _cal_date - 1;
  output;
end;
drop _eb_key _cal_date;
run;

proc sort data=want;
by cal_date eb_key;
run;

[NikosStratis]

Hello! Thank you very much for your response, and my apologize for being late, I was stuck in a 3,5 hour meeting…

I tried the code you sent, but unfortunately it did not produce the results that I need.

It brought back all the Calendar dates correctly but the transaction dates were all the same

It brought back this:

EB_Key                                                 Cal_Date              Tr_date                No01

19MAR2021        20MAR2021        11948572_032_Y_N         9

19MAR2021        20MAR2021        11955207_EPN8700_Y_N              3

19MAR2021        20MAR2021        11987246_HAN8863_Y_N             11

19MAR2021        20MAR2021        11974312_ATB3768_Y_N              7

19MAR2021        20MAR2021        11961477_NIZ3630_Y_N               2

19MAR2021        20MAR2021        11980670_IKN8318_Y_N               1

19MAR2021        20MAR2021        11994045_YMZ5501_Y_N             1

20MAR2021        20MAR2021        11994045_YMZ5501_Y_N             1

20MAR2021        20MAR2021        11987246_HAN8863_Y_N             11

20MAR2021        20MAR2021        11980670_IKN8318_Y_N               1

20MAR2021        20MAR2021        11974312_ATB3768_Y_N              7

20MAR2021        20MAR2021        11955207_EPN8700_Y_N              3

20MAR2021        20MAR2021        11961477_NIZ3630_Y_N               2

20MAR2021        20MAR2021        11948572_032_Y_N         9

21MAR2021        20MAR2021        11948572_032_Y_N         9

21MAR2021        20MAR2021        11994045_YMZ5501_Y_N             1

21MAR2021        20MAR2021        11987246_HAN8863_Y_N             11

21MAR2021        20MAR2021        11980670_IKN8318_Y_N               1

21MAR2021        20MAR2021        11974312_ATB3768_Y_N              7

21MAR2021        20MAR2021        11961477_NIZ3630_Y_N               2

21MAR2021        20MAR2021        11955207_EPN8700_Y_N              3

22MAR2021        20MAR2021        11994045_YMZ5501_Y_N             2

22MAR2021        20MAR2021        11987246_HAN8863_Y_N             13

22MAR2021        20MAR2021        11980670_IKN8318_Y_N               1

22MAR2021        20MAR2021        11974312_ATB3768_Y_N              5

22MAR2021        20MAR2021        11961477_NIZ3630_Y_N               3

22MAR2021        20MAR2021        11955207_EPN8700_Y_N              1

22MAR2021        20MAR2021        11948572_032_Y_N         8

I need the data to look like the sample below.

Cal_Date              Tr_Date                EB_Key                                                 NO01

'19MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,                               1

'19MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,                                 1

'19MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,                                3

'19MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,                                 2

'19MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,                                7

'19MAR2021'd,'19MAR2021'd,11948572_032_Y_N,                                          9

'19MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,                               11

'20MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,                               1

'20MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,                                 1

'20MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,                                3

'20MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,                                 2

'20MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,                                7

'20MAR2021'd,'19MAR2021'd,11948572_032_Y_N,                                          9

'20MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,                               11

'21MAR2021'd,'19MAR2021'd,11994045_YMZ5501_Y_N,                               1

'21MAR2021'd,'19MAR2021'd,11980670_IKN8318_Y_N,                                 1

'21MAR2021'd,'19MAR2021'd,11955207_EPN8700_Y_N,                                3

'21MAR2021'd,'19MAR2021'd,11961477_NIZ3630_Y_N,                                 2

'21MAR2021'd,'19MAR2021'd,11974312_ATB3768_Y_N,                                7

'21MAR2021'd,'19MAR2021'd,11948572_032_Y_N,                                          9

'21MAR2021'd,'19MAR2021'd,11987246_HAN8863_Y_N,                               11

'22MAR2021'd,'22MAR2021'd,11994045_YMZ5501_Y_N,                               2

'22MAR2021'd,'22MAR2021'd,11980670_IKN8318_Y_N,                                 1

'22MAR2021'd,'22MAR2021'd,11955207_EPN8700_Y_N,                                1

'22MAR2021'd,'22MAR2021'd,11961477_NIZ3630_Y_N,                                 3

'22MAR2021'd,'22MAR2021'd,11974312_ATB3768_Y_N,                                5

'22MAR2021'd,'22MAR2021'd,11948572_032_Y_N,                                          8

'22MAR2021'd,'22MAR2021'd,11987246_HAN8863_Y_N,                               13

In other words, when the Tr_Date is missing I need it to bring the previous days (Tr_Day)

Next to the Cal_Date.

Can you, please, give it a second look?

Thanks again!

[Kurt_Bremser · Accepted solution]

When you run my code AS I POSTED IT, it brings back EXACTLY your wanted result:

EB_Key	cal_date	tr_date	Num01
11948572_032_Y_N	2021-03-19	2021-03-19	9
11955207_EPN8700_Y_N	2021-03-19	2021-03-19	3
11961477_NIZ3630_Y_N	2021-03-19	2021-03-19	2
11974312_ATB3768_Y_N	2021-03-19	2021-03-19	7
11980670_IKN8318_Y_N	2021-03-19	2021-03-19	1
11987246_HAN8863_Y_N	2021-03-19	2021-03-19	11
11994045_YMZ5501_Y_N	2021-03-19	2021-03-19	1
11948572_032_Y_N	2021-03-20	2021-03-19	9
11955207_EPN8700_Y_N	2021-03-20	2021-03-19	3
11961477_NIZ3630_Y_N	2021-03-20	2021-03-19	2
11974312_ATB3768_Y_N	2021-03-20	2021-03-19	7
11980670_IKN8318_Y_N	2021-03-20	2021-03-19	1
11987246_HAN8863_Y_N	2021-03-20	2021-03-19	11
11994045_YMZ5501_Y_N	2021-03-20	2021-03-19	1
11948572_032_Y_N	2021-03-21	2021-03-19	9
11955207_EPN8700_Y_N	2021-03-21	2021-03-19	3
11961477_NIZ3630_Y_N	2021-03-21	2021-03-19	2
11974312_ATB3768_Y_N	2021-03-21	2021-03-19	7
11980670_IKN8318_Y_N	2021-03-21	2021-03-19	1
11987246_HAN8863_Y_N	2021-03-21	2021-03-19	11
11994045_YMZ5501_Y_N	2021-03-21	2021-03-19	1
11948572_032_Y_N	2021-03-22	2021-03-22	8
11955207_EPN8700_Y_N	2021-03-22	2021-03-22	1
11961477_NIZ3630_Y_N	2021-03-22	2021-03-22	3
11974312_ATB3768_Y_N	2021-03-22	2021-03-22	5
11980670_IKN8318_Y_N	2021-03-22	2021-03-22	1
11987246_HAN8863_Y_N	2021-03-22	2021-03-22	13
11994045_YMZ5501_Y_N	2021-03-22	2021-03-22	2

As you can see, all tr_dates are from March 19 for cal_date March 20 and 21.

[NikosStratis]

Hello again!
You are absolutely right!
I did something wrong with my date formats, sorry for the inconvenience!
It work great!
Thank you so much!