topic Re: Adding incremental rows based on start date and end date in SAS Programming

Adding incremental rows based on start date and end date

Vidhee — Mon, 25 Jul 2022 22:53:20 GMT

Hello,

I want to add rows based on 2 conditions.

data have-

Id	Open Date	Close Date	Indicator
123	Apr-22	Jun-22	0
456	Apr-22	Apr-22	0
789	Apr-22	Apr-22	1

Here add rows from open date to close date when Open date < Close date. Another condition when Open date = Close date and Indicator = 1 then add rows till today.

Data want -

Id	Open Date	Close Date	Indicator	Datecounter
123	Apr-22	Jun-22	0	Apr-22
123	Apr-22	Jun-22	0	May-22
123	Apr-22	Jun-22	0	Jun-22
456	Apr-22	Apr-22	0	Apr-22
789	Apr-22	Apr-22	1	Apr-22
789	Apr-22	Apr-22	1	May-22
789	Apr-22	Apr-22	1	Jun-22
789	Apr-22	Apr-22	1	Jul-22

Thanks in advance.

Re: Adding incremental rows based on start date and end date

PaigeMiller — Mon, 25 Jul 2022 23:33:57 GMT

UNTESTED CODE

data want;
    set have;
    if indicator=0 then n_months=intck('month',opendate,closedate);
    else if indicator=1 then n_months=intck('month',opendate,today());
    do i=0 to n_months;
         datecounter=intnx('month',opendate,i,'b');
         output;
    end;
    format datecounter monyy7.;
    drop i;
run;

If you want tested code, do not provide data as screen captures or Excel files. Data must be provided as working SAS data step code, and not in any other format — please test the code to make sure it works before posting it.

Also, the above code assumes that opendate and closedate are actual numeric SAS date values, which you did not say they were, and is another reason why the code is untested.

Re: Adding incremental rows based on start date and end date

ballardw — Mon, 25 Jul 2022 23:39:22 GMT

Are your "dates" actually SAS date values or just character values impersonating dates?

You provide instructions for Open date= Close date AND indicator=1. What about Open date=Close date and Indicator = 0 (or missing)? When you start writing exceptions you should be very clear to state them. Listing an example is not always sufficient.

My take:

data have;
   input Id	OpenDate :anydtdte.	CloseDate :anydtdte. Indicator;
   format opendate closedate monyy6.;
datalines;
123	Apr-22	Jun-22	0
456	Apr-22	Apr-22	0
789	Apr-22	Apr-22	1
;

data want;
   set have;
   datecounter=opendate;
   if indicator ne 1 then do;
      do until (datecounter gt closedate);
         output;
         datecounter = intnx('month',datecounter,1);
      end;
   end;
   else if opendate=closedate then do until (datecounter gt today());
         output;
         datecounter = intnx('month',datecounter,1);
   end;

   format datecounter monyy6.;
run;

Personally I believe anyone actually using 2-digit years deserves what eventually comes to them. Look up Y2K.

@Vidhee wrote:

Hello,

I want to add rows based on 2 conditions.

data have-

Id Open Date Close Date Indicator

123 Apr-22 Jun-22 0

456 Apr-22 Apr-22 0

789 Apr-22 Apr-22 1

Here add rows from open date to close date when Open date < Close date. Another condition when Open date = Close date and Indicator = 1 then add rows till today.

Data want -

Id Open Date Close Date Indicator Datecounter

123 Apr-22 Jun-22 0 Apr-22

123 Apr-22 Jun-22 0 May-22

123 Apr-22 Jun-22 0 Jun-22

456 Apr-22 Apr-22 0 Apr-22

789 Apr-22 Apr-22 1 Apr-22

789 Apr-22 Apr-22 1 May-22

789 Apr-22 Apr-22 1 Jun-22

789 Apr-22 Apr-22 1 Jul-22

Thanks in advance.

Re: Adding incremental rows based on start date and end date

mkeintz — Tue, 26 Jul 2022 02:16:51 GMT

AS @ballardw asked, you haven't said what you want when opendate=closedate and indicator=0.

Absent that howerver:

data have;
   input Id	OpenDate :anydtdte.	CloseDate :anydtdte. Indicator;
   format opendate closedate monyy6.;
datalines;
123	Apr-22	Jun-22	0
456	Apr-22	Apr-22	0
789	Apr-22	Apr-22	1
run;

data want;
  set have;
  do datecounter=opendate by 0 while (datecounter<=ifn(indicator=1 and opendate=closedate,today(),closedate));
    output;
    datecounter=intnx('month',datecounter,1);
  end;
  format datecounter date9. ;
run;

Re: Adding incremental rows based on start date and end date

Ksharp — Tue, 26 Jul 2022 12:28:58 GMT

data have;
infile cards expandtabs;
input Id OpenDate :monyy7. CloseDate :monyy7. Indicator;
format OpenDate CloseDate monyy7.;
cards;
123 Apr-22 Jun-22 0
456 Apr-22 Apr-22 0
789 Apr-22 Apr-22 1
;

data want;
 set have;
 do date=OpenDate to CloseDate;
   if month ne month(date) then output;
   month=month(date);
 end;

 if Indicator=1 and OpenDate=CloseDate then do;
   do date=OpenDate to date();
     if month ne month(date) then output;
     month=month(date);
   end;
 end;
 format date monyy7.;
 drop month;
run;