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

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
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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

David_Billa
Rhodochrosite | Level 12

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.

ballardw
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 1200 views
  • 0 likes
  • 2 in conversation