DO WHILE LOOP

[Arsene83]

Hello,

I have data set with data as BELOW:

INVOICE_NO	ISSUE_DATE	CLOSE_DATE
1	2016-09-08	2016-09-15
2	2016-09-09	2016-09-27
3	2016-09-10
4	2016-09-11
5	2016-09-12
6	2016-09-13
7	2016-09-14	2016-09-29
8	2016-09-15	2016-10-06
9	2016-09-16	2016-09-26

W need to generate table with new column REF_DATE. The REF_DATE should cotain all dates between ISSUE_DATE and CLOSE_DATE and if invoice is not CLOSED (CLOSE_DATE is null) max REF_DATE should be Today's date.

I can do this with DO loop but I would like to try DO WHILE, is there any chance to do this with DO WHILE?

[Reeza]

Yes, it can be done with a do while. 

The logic is 

While date < min(issue_date, today())

This isn't valid SAS syntax, I'll leave that to you. 

[Arsene83]

I thought so, but I'm doing somethig wrong. Could you check what's wrong?

data DO_WHILE_OUT;
      set DO_WHILE_IN ;
       REF_DATE = ISSUE_DATE;
           do While (REF_DATE < min(CLOSE_DATE, Today()));
                 REF_DATE = ISSUE_DATE+1;
                 output;
           end;

FORMAT REF_DATE yymmdd10.;
run;

[RW9]

Well, the first thing which is wrong is not providing all the information.  Post test data in the form of a datastep - otherwise we can't see the structure.  Are those "date" variables dates, or are the text, text will fail.  Also post log messages, we don't know "whats wrong".

Now in your issue:

data have;
  length issue_date close_date $50;
  infile datalines missover;
  input invoice_no issue_date $ close_date $;
datalines;
1	2016-09-08	2016-09-15
3	2016-09-10	 
;
run;

data want;
  set have;
  do ref_date=input(issue_date,yymmdd10.) to ifn(close_date="",today(),input(close_date,yymmdd10.));
    output;
  end;
  format ref_date date9.;
run;

The above works, however what if you have partial dates, or the issue date is missing, or, as you are using text apparently for dates, they are not dates?

Just to add, as you have a fixed start point, and a fixed end point, there is no beneifts to using do while.

[PeterClemmensen · Accepted solution]

in your do while loop you have sending your program into an infinite loop 🙂

the first line in the loop should be

REF_DATE = REF_DATE+1;

[Arsene83]

Thanks !

[Kurt_Bremser]

---

@Arsene83 wrote:

I thought so, but I'm doing somethig wrong. Could you check what's wrong?

data DO_WHILE_OUT;
      set DO_WHILE_IN ;
       REF_DATE = ISSUE_DATE;
           do While (REF_DATE < min(CLOSE_DATE, Today()));
                 REF_DATE = ISSUE_DATE+1;
                 output;
           end;

FORMAT REF_DATE yymmdd10.;
run;

---

data DO_WHILE_OUT;
set DO_WHILE_IN ;
REF_DATE = ISSUE_DATE;
do While (REF_DATE < min(CLOSE_DATE, Today()));
  *REF_DATE = ISSUE_DATE+1;
  * This statement is wrong, as REF_DATE will always be set to the same value, causing an endless loop;
  ref_date = ref_date + 1;
  * correct version;
  output;
end;
FORMAT REF_DATE yymmdd10.;
run;

Keep in mind that SAS code is case-insensitive, so there's no use in using capitals. Most contributors here find non-capitalized code more pleasing to the eyes.

[Kurt_Bremser]

PS and if your new dataset should start with issue_date, a slight correction is necessary:

data do_while_out;
set do_while_in ;
format ref_date yymmdd10.;
ref_date = issue_date;
do while (ref_date <= min(close_date, today()));
  output;
  ref_date = ref_date + 1;
end;
run;