Hello @ballardw, @Reeza and @Tom , I tried to write code to reach my aim. I think, If we go over the following code. maybe I can reach my aim. I'm getting Have1's and Have2's StartDates seperately and calculate Periods for both datasets. Then I create the &MonthHave2. macro variable by getting vount of first month of Have2 data set. MonthHave2 variable gives me the number of months. In Want3 step, I get the matching month values. By the help of Proc Sql statement, I'm getting the Period of Have1 data set which is equal to first month of Have2 data set. In Want4 step, I tried to assing new periods of Have2 data set. But sometimes some periods do not come, this cause of a problem.s you can see and observe my problem much better in bottom image. 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;
%Let StartDate=01DEC1987;/*Have1*/
%Let EndDate=31OCT1990;/**Have1*/
%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 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 StartDate3=01MAR1989;/*Have2*/
%Let EndDate=31OCT1990;/*Have2*/
%Let StartDate4 = %Sysfunc(Intnx(Month,"&StartDate3"d,0,b))-1;
%Let StartDate4 = %Sysfunc(PutN(&StartDate4,Date9));
%Let Nperiods2=%Sysfunc(Ceil(%Sysfunc(Intck(Month,"&StartDate4"d,"&EndDate"d))/3));
%Put &=NPeriods2;
%Let MonthHave2=%Sysfunc(Month("&StartDate3."d));
%Put &MonthHave2;
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);
MonthHave2=&MonthHave2.;/*Defining first month of Have2*/
MonthHave1=Month(Pro_End_Date_Ym);
Run;
Data Want2;
Set Have2;
If Pro_End_Date_Ym < "&StartDate3"d Or Pro_End_Date_Ym > "&EndDate"d Then period=0;
Else Period = &NPeriods2 - Int(Intck('Month',Pro_End_Date_Ym,"&EndDate"d)/3);
Run;
Data Want3/*(Obs=1)*/;
Set Want1;
If MonthHave1=MonthHave2;
Run;
Data Want4;
Set Want3(Obs=1);
Run;
Proc Sql NoPrint;
Select Period Into:NewPer From Want4;
Quit;
%Put &NewPer;
Data Want5;
Set Want2;
By period;
If Period=1 Then NewPeriod=&NewPer.;
Else If First.Period Then NewPeriod+1;
Run;
... View more
