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.
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.
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.?
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.