Preparing Two Data Sets Which Trigger Each Other

[ertr]

Hello everyone,

In addition to my following discussion, I would like to ask some additional questions to you.

https://communities.sas.com/t5/Base-SAS-Programming/Dynamic-Macro-Loop-To-Create-An-Automatic-Loop/m...

Thanks to @Tom and @Reeza I could do the foregoing task. Now, I have a little bit harder task to do it. 

This time I have two data sets and the secondary data set will be affected by the primary data set. Have1 is my first data set and Have2 is my secondary data set.

We found the count of Period by writing starting and ending dates. Critical point is that we count every three months as one Period starting from the end date. @Tom's code can do this properly.

My question is related to adjust the Secondary(Have2) data set for first(Have1) data set.Which range period of Have1 data set is match with the first month of Have2 data set, then that number of period will be same with first month of Have2 data set. It can come to you a little bit complicated. To be more precise, I added an image the bottom of my question.

Data Have;
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
10999
11100
11150
11250
;
Run;
Data Have2;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
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 want;
  set have;
  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;

Can anybody help me, please?

Thank you

[Reeza]

The only difference I see is period is 4 less. Can you just subtract 4 from your period variable?

[ertr]

Hello,

My question is not so simple, the main point is that months should match with each other. The second data set's period can start 1,2,3 or 4.

I prepared four data set as below, Have1 is my main data set and Have2, Have3 and Have4 are one of the secondary data sets.

For Have2 data set, it should start 2nd Period because March has 2nd Period in Have1 data set.(Without considering year)

For Have3 data set, it should start 3rd Period because July has 3rd Period in Have1 data set.(Without considering year)

For Have4 data set, it should start 4nd Period because October has 4th Period in Have1 data set.(Without considering year)

But Pro_End_Date_Ym variable can change for other data sets, so I need dynamic solution. I prepared a image as below, maybe it can help me to tell my aim to you.

Data Have;
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
10999
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;
Data Have3;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10800
10870
10880
10991
10999
11100
11150
11250
;
Run;
Data Have4;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10880
10991
10999
11100
11150
11250
;
Run;

[ertr]

Hello again,

Can anybody have an idea about my question? Can anyone lead me if s/he has ideas that how to do?

Thanks

[ballardw]

I'm sorry but I cannot tell what you actually want for your output.

Are you trying to put data into what appears to be excel with things lined up?

Or are you just trying to have the "period" variable value the same across different definitions of data data sets?

If I have dates I tend to use the DATE functions to create things as much as possible. For instance if you had something like

Period = put(date,yyq.);

Then the period values would be based on actual calendar quarters and not on any arbitray order of occurence. You could adjust the date using intnx by a month one way other as needed in the put to get your 3 months intervals.

Have you posted an actual data set of what you expect the final output to be? I'm not sure that the PICTURES are actually doing that.

[ertr]

Hello,

Let's say I have Have1 and Have2 data set as below. Data Want meet my demand but I need to find a code to write in Data Want2 data step. The period should start "2" because in Have1 data set March included in Period 2 because Have2 start with March then it should start with Period 2

I think, I do not need long code statements, I can resolve with two or three statements but it is little bit complicated, Can anybody help me, please?

Thank you,

Data Have;
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
10999
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 want;
  set have;
  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;
/*I'm searching for a code to writer here
It should match with March in Have1 data set
and the period start with 2 which is March's period in Have1 data set */
Run;

[ballardw]

1) Please show the exact desired output

2) Why are you attempting to match (apparently) on what is a temporary conditon of order of appearance in a data set??? An actual value related to values of interest makes much more sense as then the matching criteria actuall means something. Your current approach requires recoding every time either data set changes which is somewhat combersome.

You also need to explain what the actual role of startdate and enddate are if they do anything other cause the changing values for the number of periods.

[ertr]

Hello,

1- > Here is my desired output

2-) Actually, it is little bit related to statistic, these data sets are sample data sets. It is hard to explain but after this step I'm doing some addition steps then I compare the datas depending on Periods so Periods are important for me. This is exactly the point which I hang. The result of one data set should affect the other data set, so that is the reason why I'm having a difficulty. I think some advance advice can figure out my aim.

I will be appreciated If I handle this issue.

Thank you very much

[ertr]

@ballardw,

Should I express any additional explanations? Do my question and aim clear? 

[Reeza]

I think your making something incredibly difficult. I've never come across a date logic this complex in 10+ years of programming all sorts of reports. 

I'm going to recommend you start from scratch, show what you have and where your trying to go. Include full input and expected output data. 

Or you can keep going with this method. 

[ballardw]

Please explain in some detail how the period variable is used in your statistics.

So far all that I can see is that you want to the same result, based on DATE values, (grouping by month of a date is still using a date value) from different data sets.

If you have a master dataset of dates that you want to use with a "period" I would suggest making a format using that information. Then you could apply that date-based format to other dates and get the same result. The basic approach for this would be :

Identify you base period data, get the first and last date of the periods POSSIBLE such as 01Dec1987 and 29Feb1988 using the appropriate SAS date functions to create a CNTLIN dataset for Proc Format.

[ertr]

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;

[Cynthia_sas]

Hi:

  I still do not understand the issue. And I don't understand what the problem is with the NewPeriod values. To me, the values in WANT5 are exactly what the program was coded to do.

Since I don't understand what is wrong with NEWPERIOD starting on row 6, I am baffled. But if there is a problem with the WANT5 output, the only possible solution is to do as Reeza suggested and revisit the logic.

cynthia

[ertr]

Hello @Cynthia_sas, @ballardw, @Reeza, @Ksharp,

I know my question is little bit complicated, sorry for this complication. I will try to re-explain my question;

Have1 is my constant data set, I mean long data set but data set Have2 can change. The first month of Have2 is important because Have2 goes to Have1 and find its same month in Have1 data set but I have to say that the years are not important. For example, in my sample, March is first month of Have2 data set then Have2 will look the Have1's data set and search for first March on Have1 data set.

The first March is in the 2nd Period on Have1 data set, for this reason Have2 starts with 2nd Period, if March was in the 3rd Period in Have1 then Have2 will start with 3rd Period.As you can realize first March of Have2 data set has 1989 however, first March of Have1 data set has 1988 but it is not important. We are looking for both data sets firsth months depending on Have2's first month.

Here is the data sets and image of the logic

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;

Sample for Have1 and Have3;

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 Have3;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10800
10870
10880
10991
10999
11100
11150
11250
;
Run;

If I need to tell what is @Tom's code doing;

We are getting first and end date of data sets then calculating the periods depending on rule of three months. I mean every period decreasing three months by three months. we start with end date and we are decreasing the months three by three.

%let startdate=01jun2010;
%let enddate=31mar2015;
%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 want;
  set have;
  if date < "&startdate"d or date > "&enddate"d then period=0;
  else period = &nperiods - int(intck('month',date,"&enddate"d)/3);
run;

Thank you very much

[Reeza]

If years don't matter why doesn't it match with March 1990? Why 1989? Is it exactly 1 year in future or if I applied Toms code, both period 2? 

And great they match, now what? What do you want the output to look like?

i standby my earlier assertation.