topic Creating multiple rows based on time interval between two variables in SAS Programming

Creating multiple rows based on time interval between two variables

David_Billa — Tue, 18 Aug 2020 17:18:03 GMT

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.

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

ballardw — Tue, 18 Aug 2020 17:26:12 GMT

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.?

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

David_Billa — Tue, 18 Aug 2020 17:41:24 GMT

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.

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

ballardw — Tue, 18 Aug 2020 19:32:29 GMT

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-data-AKA-generate/ta-p/258712 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.