topic Re: DO WHILE LOOP in SAS Programming

DO WHILE LOOP

Arsene83 — Fri, 25 Nov 2016 08:23:58 GMT

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?

Re: DO WHILE LOOP

Reeza — Fri, 25 Nov 2016 08:45:44 GMT

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.

Re: DO WHILE LOOP

Arsene83 — Fri, 25 Nov 2016 09:14:01 GMT

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;

Re: DO WHILE LOOP

RW9 — Fri, 25 Nov 2016 10:03:02 GMT

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.

Re: DO WHILE LOOP

PeterClemmensen — Fri, 25 Nov 2016 09:44:13 GMT

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;

Re: DO WHILE LOOP

Kurt_Bremser — Fri, 25 Nov 2016 09:45:18 GMT

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

Re: DO WHILE LOOP

Kurt_Bremser — Fri, 25 Nov 2016 09:49:48 GMT

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;

Re: DO WHILE LOOP

Arsene83 — Fri, 25 Nov 2016 11:07:45 GMT

Thanks !