Generate indicator in range date

[Marco Errico]

Dear all, I have the following dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN str25 Suspensionsexpectedtobetri str8 SuspensionLevel int(susp_start susp_end Asofdate)
"SE0007691613" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0060636678" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0010311471" "Suspended" "EU LEVEL" 21255 21439 21250
"FI4000062385" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0004712375" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BLT1Y088" "Suspended" "EU LEVEL" 21255 21439 21250
"DE000TUAG000" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0006422390" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B1WY2338" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0006625471" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0005622542" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0005203376" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0010112524" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0007640156" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0010253921" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0010776944" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0007158829" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000120693" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0009895292" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000044448" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003856405" "Suspended" "EU LEVEL" 21255 21439 21250
"JE00B8KF9B49" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0010937066" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BYSRJ698" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003043418" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000131757" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BRB37M78" "Suspended" "EU LEVEL" 21255 21439 21250
"NO0010736879" "Suspended" "EU LEVEL" 21255 21439 21250
"PTZON0AM0006" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B15FWH70" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0004176001" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B02L3W35" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0004082847" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003201198" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B53P2009" "Suspended" "EU LEVEL" 21255 21439 21250
"FI0009800643" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B18V8630" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BZ21RF93" "Suspended" "XPOS"     21255 21439 21250
"GB0001411924" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B1Z4ST84" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0013153541" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0000695876" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BZ4BQC70" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0000148884" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0013154002" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B2PDGW16" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0010218429" "Suspended" "EU LEVEL" 21255 21439 21250
"FI4000062781" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BYX91H57" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000033409" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BN3ZZ526" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B3Y2J508" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0000961622" "Suspended" "EU LEVEL" 21255 21439 21250
"CS0005021351" "Suspended" "EU LEVEL" 21255 21439 21250
"AT0000730007" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0000310336" "Suspended" "EU LEVEL" 21255 21439 21250
"PTSEM0AM0004" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BF5SDZ96" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B06QFB75" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0031215220" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B1YKG049" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0006834344" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0060477503" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BZ6STL67" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BY9D0Y18" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000051807" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003173629" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0007188757" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BJTNFH41" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0000888691" "Suspended" "EU LEVEL" 21255 21439 21250
"FI0009002471" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0060336014" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003007728" "Suspended" "EU LEVEL" 21255 21439 21250
"DK0060257814" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0007100599" "Suspended" "EU LEVEL" 21255 21439 21250
"NO0010748866" "Suspended" "MERK"     21255 21439 21250
"SE0007185418" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0009633180" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0007958233" "Suspended" "EU LEVEL" 21255 21439 21250
"DE000EVNK013" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0000288876" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003683528" "Suspended" "EU LEVEL" 21255 21439 21250
"ZAE000156550" "Suspended" "EU LEVEL" 21255 21439 21250
"FR0000050353" "Suspended" "EU LEVEL" 21255 21439 21250
"SE0006509949" "Suspended" "EU LEVEL" 21255 21439 21250
"LU0307018795" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00BVGBWW93" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0006886666" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0010545661" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B0LCW083" "Suspended" "EU LEVEL" 21255 21439 21250
"IT0003497176" "Suspended" "EU LEVEL" 21255 21439 21250
"NO0003733800" "Suspended" "EU LEVEL" 21255 21439 21250
"NL0011279492" "Suspended" "EU LEVEL" 21255 21439 21250
"GB0008782301" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B0744359" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B6YTLS95" "Suspended" "EU LEVEL" 21255 21439 21250
"GRS393503008" "Suspended" "XPOS"     21255 21439 21250
"FR0004254035" "Suspended" "EU LEVEL" 21255 21439 21250
"ZAE000015228" "Suspended" "EU LEVEL" 21255 21439 21250
"GB00B10RZP78" "Suspended" "EU LEVEL" 21255 21439 21250
end
format %td susp_start
format %td susp_end
format %td Asofdate

Is a monthly information uploaded by a regulator. The date that information is uploaded is identified by -Asofdate-
I'm trying to expand to dailydataset, from 1st january 2018 to 31 dec 2021, for each ISIN. (so each ISIN need days from 1st January 2018 to 31 dec 2021)

I explored the option of -tsfill-, but the problem is that tsfill just generate days until last value of -Asofdate- for each ISIN.

The final purpose is to create, for each ISIN, an indicator that equals to 1 for the days betwen -susp_start- and -susp_end.-

Do you have any kind suggestions?
Many thanks

[Clyde Schechter]

I'm not entirely sure I understand what you want, but it might be this:

isid ISIN Asofdate, sort
gen long obs_no = _n
expand susp_end - susp_start + 1
by obs_no, sort: replace Asofdate = susp_start + _n - 1
gen byte wanted_indicator = inrange(Asofdate, susp_start, susp_end)
tempfile holding
save `holding'

keep ISIN
duplicates drop
expand td(31dec2021)-td(1jan2018) + 1
by ISIN, sort: gen Asofdate = td(31dec2017) + _n
merge 1:1 ISIN Asofdate using `holding', keep(match master) nogenerate
replace wanted_indicator = 0 if missing(wanted_indicator)
format Asofdate %td
drop obs_no

Added: If there is at least one observation with Asofdate = 1jan2018 and at least one observation with Asofdate = 31dec2021, then you don't need the rigmarole above. You can just use

xtset ISIN Asofdate
tsfill, full

and every ISIN will be paired up with every date from 1jan2018 through 31dec2021.

[Marco Errico]

Thank you, Clyde Schechter .

My fault that I was using -tsfill without the -full- option.
Apologies for not fully looking the various options. Both block of codes that you suggest work perfectly.

Thank again for your time