Search code examples
sqlloopsdatesasformat

Proq sql query in SAS

I have a table with IDs and dates which looks like

|IDs|Start_Date|End_Date|
|---|----------|--------|
|1|202105|202107|
|2|202201|202205|
|3|202101|202301|

And I need the output with all the dates in between basically uniformly incremented BY MONTH (format date6.) so that I can use the output table for further joins.

|IDs|New_Date|
|---|--------|
|1|202105|
|1|202106|
|1|202107|
|2|202201|
|2|202202|
|2|202203|
|2|202204|
|2|202205|
.
.

What kind of functions would let me do that in the most simplest and efficient way using either proq sql or data step in SAS?

Was thinking of trying loops in data step or INTCK function.

Solution

  • If you want to increment date/time/datetime values by something other than the units (days/seconds/seconds) they are stored in then use a combination of INTNX() and an offset counter. To calculate the number of intervals use the INTCK() function.

    So to generate one observation for every month between START and END you could use.

    data want ;
  set have;
  do offset=0 to intck('month',start,end);
    date = intnx('month',start,offset);
    output;
  end;
  format date date9.;
run;