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.
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.
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;
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.