Splitting one row with two dates into multiple rows by month

Sas_Act_114 — Tue, 02 Mar 2021 19:01:43 GMT

Hello,

I have a table with two date variables that make a date range (as an example, Var1 is 01/01/2019 and Var2 is 06/30/2019). I want to take that rows and split it into multiple rows, with a row for each month within the date range. Thus in the example I have, the one record would be split into six records (201901, 201902, 201903, etc..). I know this is somehow possible with the INTCK and INTNX function and using some kind of loop, but I can not figure out how to use them properly to get the results I want. Any help would be appreciated, thank you!

Re: Splitting one row with two dates into multiple rows by month

PeterClemmensen — Thu, 04 Mar 2021 11:07:29 GMT

Use the Intnx Function like this

data have;
input (Var1 Var2)(:mmddyy10.);
format Var: mmddyy10.;
datalines;
01/01/2019 06/30/2019
;

data want(drop = i);
   set have;
   do i = 0 by 1 while (1);
      dt = intnx('month', Var1, i, 'b');
      if dt > Var2 then leave;
      output;
   end;

   format dt yymmn6.;
run;

topic Re: Splitting one row with two dates into multiple rows by month in SAS Programming

Splitting one row with two dates into multiple rows by month

Re: Splitting one row with two dates into multiple rows by month