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