BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Arsene83
Calcite | Level 5

Hello,

 

I have data set with data as BELOW:

 

INVOICE_NOISSUE_DATECLOSE_DATE
12016-09-082016-09-15
22016-09-092016-09-27
32016-09-10 
42016-09-11 
52016-09-12 
62016-09-13 
72016-09-142016-09-29
82016-09-152016-10-06
92016-09-162016-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?

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
Calcite | Level 5

Thanks !

Kurt_Bremser
Super User

@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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1145 views
  • 1 like
  • 5 in conversation