BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ertr
Quartz | Level 8

Hello everyone,

 

I have a question which is little bit complicated.

 

I have a sample data set which includes dates, I need to determine these dates periods. I have a sample code as below.

 

Data Have;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10826
10827
10828
10829
10830
10850
10870
10880
10991
10999
;
Run;

 

Months start with EndDate and decreasing three by three but when it comes StartDate it needs to catch up the excess between EndDate and StartDate. I mean first period months can increase one by one. If you can investigate my following code, you can understand better.

 

/*Purpose*/
%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
/*Purpose*/

Data Want; Set Have; Length Period 4.; Period = 0; if '31JUL1989'd < PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;/*Decreasing 1 month
because it cannot excess the StartDate*/ if '31AUG1989'd < PRO_END_DATE_YM <= '30NOV1989'd then Period = 2;/*Decreasing 3 months*/ if '30NOV1989'd < PRO_END_DATE_YM <= '28FEB1990'd then Period = 3;/*Decreasing 3 months*/ Run;

My purpose is just define the StartDate and EndDate and create the dates and Periods automatically. Actually, I need to create dates and periods being macro variables. But it seems to me it cannot possible for us. I just want to give your opinions.

 

The foregoing data step gives my desired output, but I want to take them dynamically.

 

Thank you,

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You probably want to adjust the number of periods to handle the situation you have presented with using a start date that is not the end of the month.  For example try this program.  I added some values before and after the interval defined by startdate to enddate and added a IF statement to set those to PERIOD=0.

 

data have ;
  attrib date informat=date9. format=date9. ;
  input date @@ ;
cards;
01MAY2010 31MAY2010 01JUN2010 30JUN2010 01JUL2010 01AUG2010
01SEP2010 01OCT2010 01NOV2010 01DEC2010 01JAN2011 01FEB2011
01MAR2011 01APR2011 01MAY2011 01JUN2011 01JUL2011 01AUG2011
01SEP2011 01OCT2011 01NOV2011 01DEC2011 01JAN2012 01FEB2012
01MAR2012 01APR2012 01MAY2012 01JUN2012 01JUL2012 01AUG2012
01SEP2012 01OCT2012 01NOV2012 01DEC2012 01JAN2013 01FEB2013
01MAR2013 01APR2013 01MAY2013 01JUN2013 01JUL2013 01AUG2013
01SEP2013 01OCT2013 01NOV2013 01DEC2013 01JAN2014 01FEB2014
01MAR2014 01APR2014 01MAY2014 01JUN2014 01JUL2014 01AUG2014
01SEP2014 01OCT2014 31OCT2014 01NOV2014 30NOV2014 01DEC2014
31DEC2014 01JAN2015 01FEB2015 01MAR2015 31MAR2015 01APR2015
;
%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;

To see the results by period you could run this little data _null_ step since the input was sorted by DATE.

 

data _null_;
  set want ;
  by period notsorted;
  if first.period then put / period= z2. +1 @;
  put date @ ;
run;

Whch produces this list.

period=00  01MAY2010 31MAY2010
period=01  01JUN2010 30JUN2010
period=02  01JUL2010 01AUG2010 01SEP2010
period=03  01OCT2010 01NOV2010 01DEC2010
period=04  01JAN2011 01FEB2011 01MAR2011
period=05  01APR2011 01MAY2011 01JUN2011
period=06  01JUL2011 01AUG2011 01SEP2011
period=07  01OCT2011 01NOV2011 01DEC2011
period=08  01JAN2012 01FEB2012 01MAR2012
period=09  01APR2012 01MAY2012 01JUN2012
period=10  01JUL2012 01AUG2012 01SEP2012
period=11  01OCT2012 01NOV2012 01DEC2012
period=12  01JAN2013 01FEB2013 01MAR2013
period=13  01APR2013 01MAY2013 01JUN2013
period=14  01JUL2013 01AUG2013 01SEP2013
period=15  01OCT2013 01NOV2013 01DEC2013
period=16  01JAN2014 01FEB2014 01MAR2014
period=17  01APR2014 01MAY2014 01JUN2014
period=18  01JUL2014 01AUG2014 01SEP2014
period=19  01OCT2014 31OCT2014 01NOV2014 30NOV2014 01DEC2014 31DEC2014
period=20  01JAN2015 01FEB2015 01MAR2015 31MAR2015
period=00  01APR2015

 

 

 

 

View solution in original post

30 REPLIES 30
Reeza
Super User

It's definitely possible. Use intnx to increment dates, make sure to look at the last parameter that aligns the dates to beginning or end of month. 

 

 

ertr
Quartz | Level 8

@Reeza,

 

Thank you very much for reminder, the following is going to provide me the my desired dynamic loop, however, I have some questions.

 

Why this statement doesn't work -> %Let FormattedDate=%Sysfunc(Intnx('Month',"&LatestDate."D,0,"E")); and the following code seems okay but I'm not sure, also seems little bit extended, are there any short methods?

Thank you

 

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
/*Hold variable in the Macro variable*/
Proc Sql;
Select Pro_End_Date_Ym Into:Dates Separated By " " From Have
Order By Pro_End_Date_Ym Desc;
Quit;

%Let Count=%Sysfunc(CountW(&Dates));/*Count of Dates*/
%Let LatestDate=%Scan(&Dates,-1);/*Hold latest Value to use in further steps*/
%Let FormattedDate=%Sysfunc(Intnx('Month',"&LatestDate."D,0,"E")); /*Why it doesn't work???*/
%Put &Dates;
%Put &Count;
%Put &LatestDate;
%Put &FormattedDate;
/*Convert values to latest day of month*/
%Macro Months(Month);
Data Want2(Drop=Pro_End_Date_Ym);
Set Have;
		%Do i=1 %To &Count.;
		%Let Var&i = %Scan(&Dates,&i,%str( ));
			Months=Intnx('Month',"&&Var&i"D,0,"E"); Output; Format Months Date9.;
		%End;
Run;
%Mend Months;
%Months; /*Why it brings 100 row instead of 10*/
/*Hold converted values in the Macro variable*/
Proc Sql;
Select Distinct Months Into:Dates2 Separated By " " From Want2
Order By Months Asc;
Quit;
%Let Count2=%Sysfunc(CountW(&Dates2));/*Count of Dates2*/
%Put &Dates2;
%Put &Count2;
%Macro Months2(Month2);
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%If &Count GT 0 %Then %Do;
	%Do j = &Count %To 1 %By -1;
	%Let Var&j = %Scan(&Dates2,&j,%Str( ));
If Intnx('Month',"&&Var&j."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&&Var&j."D,0,"E")Then Period = &j; 
    %End;
	%IF PRO_END_DATE_YM <=&LatestDate. %Then %Do;
	If Intnx('Month',"&StartDate."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&LatestDate."D,0,"E")Then Period = &j; 
%End;
%End;
Run;
%Mend Months2;
%Months2;
Reeza
Super User

Wow. I don't think you need that much macro logic...start with your first macro, months. You get 100 results because you loop 10 times over each record. I think you only need the data step and no macro logic there. Try simplifying that as a starter. 

Reeza
Super User

You don't quote parameters in %sysfunc. 

Maybe post what you want your output to be and we can help you get there without all of that macro logic.

 

%Let FormattedDate=%Sysfunc(Intnx(Month,"&LatestDate."D,0,E)); 

Tom
Super User Tom
Super User

What are you actually trying to do?  Hard to tell but it looks like you are just dividing a time interval into periods and calculating for the dates in your dataset which period it falls into?  Why not just do that directly?

 

So if you have a variable DATE (perhaps your PRO_END_DATE_YM variable) and you want see how many months it is away from the START date then use the INTCK() function.

 

%let start=31JUL1989 ;

data want ;
  set have ;
  month_num = intck('month',"&start"d,DATE);
run;
 

 

Reeza
Super User

I agree with @Tom. Additionally, you may be able to use SAS QTR to calculate some of these and you don't need to create custom intervals depending on your requirements. 

ertr
Quartz | Level 8

Hello  @Reeza and @Tom;

 

Thank you for your explanations, I'm going to try to explain what I am trying to do?

 

Actually, the following code creates my desired output

 

 

Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if '31JUL1989'd < PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;
/*Decreasing 1 month because it cannot excess the StartDate*/ if '31AUG1989'd < PRO_END_DATE_YM <= '30NOV1989'd then Period = 2;
/*Decreasing 3 months*/ if '30NOV1989'd < PRO_END_DATE_YM <= '28FEB1990'd then Period = 3;
/*Decreasing 3 months*/ Run;

 

I want to create this structure being dynamic.

 

But these date values for e example->"28FEB1990",  "30NOV1989", "31AUG1989" are decreasing three by three but it doesn't mean it represents quarters.

 

For example, the customer is going to write Start and End Date as parametric ->

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;

But in my foregoing example when it comes "31AUG1989" there is just one month between "31AUG1989" and StartDate("31JUL1989") so it shouldn't decrease three months it should decrease just one month.

 

My purpose is that get Start and End Date then create the Data Want which I wrote in my foregoing Data Step. I would like to remind that this is a sample data, the data will have more observations in real environment.

 

As a summary, I just determine the Start and End Date the it will create dates and periods

 

For Example ->"28FEB1990",  "30NOV1989", "31AUG1989" and for Periods -> 1, 2 and 3 will automatically.

 

Something like this

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if "&Macro2"d < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 1 month because it cannot excess the StartDate*/
if "&Macro2"d  < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 3 months*/
if "&Macro2"d < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 3 months*/
Run;

Actually the following Months2 macro helps me but as you realize before this macro I did many steps

 

%Macro Months2(Month2);
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%If &Count GT 0 %Then %Do;
	%Do j = &Count %To 1 %By -1;
	%Let Var&j = %Scan(&Dates2,&j,%Str( ));
If Intnx('Month',"&&Var&j."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&&Var&j."D,0,"E")Then Period = &j; 
    %End;
	%IF PRO_END_DATE_YM <=&LatestDate. %Then %Do;
	If Intnx('Month',"&StartDate."D,0,"E")< PRO_END_DATE_YM <=Intnx('Month',"&LatestDate."D,0,"E")Then Period = &j; 
%End;
%End;
Run;
%Mend Months2;
%Months2;

 

Reeza
Super User

Why is the first one only one month, not three like the others? That's the key logic that needs to be captured.

Besides that, it's relatively easy:

 

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;

Data Want;
sest have;
Length Period 4.; 
Period = 0;

boundary1 = "&startDate"d;
boundary2 = intnx("month", "&startDate"d, 1, 'e');
boundary3 = intnx("month", "&startDate"d, 4, 'e');
boundary4 = intnx("month", "&startDate"d, 7, 'e');

format boundary: date9.;

if boundary1 < PRO_END_DATE_YM <= boundary2 then Period = 1;/*Decreasing 1 month because it cannot excess the StartDate*/
if boundary2 < PRO_END_DATE_YM <= boundary3 then Period = 2;/*Decreasing 3 months*/
if boundary3 < PRO_END_DATE_YM <= boundary4 then Period = 3;/*Decreasing 3 months*/
Run;
ertr
Quartz | Level 8

Hello again, 

 

@Tom and @Reeza,

 

Thank you for preparing a code but Start Date and End Date can change and Periods also depends on the Start Date and End Date.

 

So I need to create this structere for Macro variables, I mean StartDate and EndDate.

 

What I mean is that what if End Date is "31AUG1990" instead of  "28FEB1990", Periods also will change for Start Date and End Date.

 

I will be happy, If I get some direction from you. Thanks

Reeza
Super User
The dates will change but rules have to stay the same. Whether its a macro or non macro solution this is true. So what are your rules.
ertr
Quartz | Level 8

Sorry @Reeza and @Tom,

 

English is not my mother language, I guess that is the reason why I cannot explain myself clear.

 

Let's say I prepared your code then I delivered the code to the customer.

 

Here is your code;

 

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;

Data Want;
sest have;
Length Period 4.; 
Period = 0;

boundary1 = "&startDate"d;
boundary2 = intnx("month", "&startDate"d, 1, 'e');
boundary3 = intnx("month", "&startDate"d, 4, 'e');
boundary4 = intnx("month", "&startDate"d, 7, 'e');

format boundary: date9.;

if boundary1 < PRO_END_DATE_YM <= boundary2 then Period = 1;/*Decreasing 1 month because it cannot excess the StartDate*/
if boundary2 < PRO_END_DATE_YM <= boundary3 then Period = 2;/*Decreasing 3 months*/
if boundary3 < PRO_END_DATE_YM <= boundary4 then Period = 3;/*Decreasing 3 months*/
Run;

Then someday they want to implement your code for different data set. Let's say they want to use following data set

 

Data Have;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10826
10827
10828
10829
10830
10850
10870
10880
10991
10999
11111
11222
11333
11444
11555
11666
;
Run;

Then they realize some of the periods comes zero and they also don't know SAS code.

 

I want to prevent this situation. Could I make myself clear?

 

I think Macro code is only solution, what do you think?

Reeza
Super User

No. Given what you've shown I still believe any logic you implement via macros can easily be accomodated in a data step.

 

The rules need to be clear.

 

What happens if the period is 0? What defines a period, is it the 3 months periods from Dec to Feb, Mar - May, Jun-Aug, Sep - Dec, except truncated for if the start date is somewhere in between?  

 

You can make it a macro to take a different data set, different variable and start period. That's fine. But the logic to calculate the periods should be implemented as a data step.

 

%macro date_intervals(datain = , variable = , date_start= , date_end =, dataout=);


data &dataout;
set &datain;

*date logic stuff goes here;


run;

%mend;

 

 

ertr
Quartz | Level 8

@Reeza and @Tom,

 

EndDate also will change like this -> 

%Let StartDate=31JUL1989;
%Let EndDate=31DEC1991;
Tom
Super User Tom
Super User

So if you want to divide the internal into 3 month "PERIODS" you can do it arithmetically. No need for loops of any kind.

 

data have;
  input date @@ ;
  informat date date9. ;
  format date date9. ;
datalines;
22AUG1989 23AUG1989 24AUG1989 25AUG1989 26AUG1989 15SEP1989
05OCT1989 15OCT1989 03FEB1990 11FEB1990 03JUN1990 22SEP1990
11JAN1991 02MAY1991 21AUG1991 10DEC1991
;;;;

%let startdate=31jul1989;

data want;
  set have;
  period = 1 + ceil((intck('month',"&startdate"d,date)-1)/3) ;
run;

It might even be possible for you to create the STARTDATE macro variable from the input data.

proc sql noprint ;
  select intnx('month',min(date),-1,'e') format=date9.
    into :startdate
  from have
  ;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 30 replies
  • 2522 views
  • 9 likes
  • 3 in conversation