Preparing Two Data Sets Which Trigger Each Other

[ertr]

@Reeza,

I do not exactly understand what you are trying to learn? Didn't my question is clear?

It should match with first March in Have1 data set because Have2 start with March

If Have2 start with July then it should match with first July on Have1 data set.

If Have2 start with August then it should match with first August on Have1 data set.

Etc.

I thought If they match, maybe I can reach my desired image. My desired image as below.

When they match, I will get the Period. For example, they match on Period 2 in Have1 data set so I will take the Period 2 and assign the Have2 as beginning Period of Have2 data set but how about other rows, how can I increase the other rows? Increasing one by one is not a solution because some periods can come as empty. 

I know the logic is complicated but my question seems clear, isn't it? I just need some direction.

Thank you

[Reeza]

This can get you started. I'm not sure how you're aligning your dates and I'll leave you to figure out the exact alignment.

This is close but not exactly what you want.

Data Have1;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10200
10300
10400
10500
10600
10700
10800
10900
10870
10880
10991
11020
11100
11150
11250
;
Run;

Data Have2;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10675
10800
10800
10870
10880
10991
10999
11100
11150
11250
;
Run;

%Let startdate=01DEC1987;
%let enddate=31OCT1990;
%let startdate2 = %sysfunc(intnx(month,"&startdate"d,0,b))-1;
%let startdate2 = %sysfunc(putn(&startdate2,date9));
%let nperiods=%sysfunc(ceil(%sysfunc(intck(month,"&startdate2"d,"&enddate"d))/3));
%put &=nperiods;
data want1;
  set have1;
  if Pro_End_Date_Ym < "&startdate"d or Pro_End_Date_Ym > "&enddate"d then period=0;
  else period = &nperiods - int(intck('month',Pro_End_Date_Ym,"&enddate"d)/3);
run;
Data want2;
  set have2;
  if Pro_End_Date_Ym < "&startdate"d or Pro_End_Date_Ym > "&enddate"d then period=0;
  else period = &nperiods - int(intck('month',Pro_End_Date_Ym,"&enddate"d)/3);
run;

proc sql;
select min(pro_end_date_ym) into :first_date
from have2;
quit;

data var_find;
set want1;
retain flag 0;
if month(Pro_End_Date_Ym)=month(&first_date) and flag=0 then do;
flag=1;
call symputx('period_start', period);
end;
run;

%put &period_start;

data want_final;
set have2;
retain start period;
format start date9.;
if _n_=1 then do;
	period=&period_start;
	start=intnx('quarter', pro_end_date_ym, 0, 'b');
end;
else period=floor(intck('month', start, pro_end_date_ym)/3)+&period_start;
run;

[ertr]

Thank you @Reeza,

Your code helped me but I do not reach my aim. It is very difficult to realise my aim. I do not have any opinion to do this 😞

Thank you

[Reeza]

In some ways I don't want to help with something that seems incredibly inefficient and complex. You seem very set in your method of doing this and unwilling to consider other options. Perhaps someone else will help, otherwise you need to restart or keep trying. 

Good luck either way. 

[ertr]

Hello @Reeza,

I hope you didn't understand me wrong. I gave up, I will try to perform alternative methods. Thank you for triying to help me 🙂

Thanks