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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.