Rhodochrosite | Level 12

## Creating multiple rows based on time interval between two variables

I want to create multiple rows based on time interval between two variables. I've the data as below. We have 'Duration' value as 3 for each 'Year_mo' (2017-03 and 2017-06) in the data below.

Branch	Segment	   Type    Year_mo	 Start_Mo	End_Mo	        Duration
71831234 7183_AS4  Cdir	   2017-03	 2017-03	2017-09	         3
71831234 7183_AS4  Cdir	   2017-06	 2017-03	2017-09	         3

Now I want to create as many as rows based on the value from the variable 'Duration' and I've to create one more variable called 'Cal_Month' which should have values in quarters between time interval of the variables 'Start_Mo' and 'End_Mo

with group by 'Year_mo'

In this case values of 'Cal_Month' should be in 2017-03, 2017-06 and 2017-09. 'Duration' value is 3 for each each 'Year_mo'. So I need 3 observations for 'Year_mo' with the respective 'Cal_Month'  values.

Desired Result is,

Branch	 Segment      Type	Year_mo	Start_Mo  End_Mo   Cal_Month
71831234  7183_AS4    Cdir	2017-03	2017-03	  2017-09   2017-03
71831234   7183_AS4   Cdir	2017-03	2017-03	  2017-09   2017-06
71831234   7183_AS4   Cdir	2017-03	2017-03	  2017-09   2017-09
71831234   7183_AS4   Cdir	2017-06	2017-03	  2017-09   2017-03
71831234   7183_AS4   Cdir	2017-06	2017-03	  2017-09   2017-06
71831234   7183_AS4   Cdir	2017-06	2017-03	  2017-09   2017-09

In real life I've data with 'Duration' from 1 to 9 and "Year_Mo' is also I have multiple Dates. To get the idea to tackle this Scenario, I just shared the sample data here.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Creating multiple rows based on time interval between two variables

This duplicates your wanted data given the starting data.

data have;
input Branch \$ Segment \$ Type \$ Year_mo :anydtdte7.
Start_Mo :anydtdte7. End_Mo :anydtdte7. Duration;
format year_mo start_mo end_mo yymmd7.;
datalines;
71831234 7183_AS4  Cdir	   2017-03	 2017-03	2017-09	         3
71831234 7183_AS4  Cdir	   2017-06	 2017-03	2017-09	         3
;

data want;
set have;
cal_month=start_mo;
do until (cal_month > end_mo);
output;
cal_month=intnx('month',cal_month,duration);
end;
format cal_month yymmd7.;

run;

You may run into interesting output if your duration value does not divide nicely in the number of months between start and end months.

Please note that I provide a way to create actual data for testing code in the form of a data step. That is the preferred manner of providing example data as then there is no need to ask questions about data types.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

3 REPLIES 3
Super User

## Re: Creating multiple rows based on time interval between two variables

First, are all of your year_mo start_mo and end_mo variables actual SAS date values, i.e. numeric with an apparent format of YYMMD7.?

Rhodochrosite | Level 12

## Re: Creating multiple rows based on time interval between two variables

Yes, all the variables (year_mo start_mo and end_mo) are SAS date values. I'm looking for logic to fit this requirement via SAS program.

Super User

## Re: Creating multiple rows based on time interval between two variables

This duplicates your wanted data given the starting data.

data have;
input Branch \$ Segment \$ Type \$ Year_mo :anydtdte7.
Start_Mo :anydtdte7. End_Mo :anydtdte7. Duration;
format year_mo start_mo end_mo yymmd7.;
datalines;
71831234 7183_AS4  Cdir	   2017-03	 2017-03	2017-09	         3
71831234 7183_AS4  Cdir	   2017-06	 2017-03	2017-09	         3
;

data want;
set have;
cal_month=start_mo;
do until (cal_month > end_mo);
output;
cal_month=intnx('month',cal_month,duration);
end;
format cal_month yymmd7.;

run;

You may run into interesting output if your duration value does not divide nicely in the number of months between start and end months.

Please note that I provide a way to create actual data for testing code in the form of a data step. That is the preferred manner of providing example data as then there is no need to ask questions about data types.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Discussion stats
• 3 replies
• 1190 views
• 0 likes
• 2 in conversation