topic Re: Fill transaction data from previous date(s) in SAS Programming

Fill transaction data from previous date(s)

NikosStratis — Tue, 23 Mar 2021 08:42:45 GMT

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.

Re: Fill transaction data from previous date(s)

Kurt_Bremser — Tue, 23 Mar 2021 09:36:52 GMT

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;

Re: Fill transaction data from previous date(s)

NikosStratis — Tue, 23 Mar 2021 13:40:28 GMT

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!

Re: Fill transaction data from previous date(s)

Kurt_Bremser — Tue, 23 Mar 2021 13:56:10 GMT

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.

Re: Fill transaction data from previous date(s)

NikosStratis — Tue, 23 Mar 2021 14:20:50 GMT

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!