topic Re: How do I output dates that are not in chronological order? in SAS Programming

How do I output dates that are not in chronological order?

vidya3 — Mon, 03 Apr 2023 06:06:56 GMT

Please help as this is very urgent.

There are 10 visits totally, Visit 1 to Visit 10. Currently I have transported the dates from column to row form, now I need to output rows with dates that are not in chronological order. These rows contain missing dates too. Please refer to Flag column in the attachment, I have flagged the rows that need to be in the output and I don't need the rows that say 'Do not output' in the output. Thanks.
Version- Sas enterprise guide 7.13

Re: How do I output dates that are not in chronological order?

Kurt_Bremser — Mon, 03 Apr 2023 07:14:32 GMT

We're working with SAS here, not with Excel 😉

So please post data the SAS way, as DATA step code with datalines. In many professional environments, download of Office files is prevented for security reasons, and Excel file sdo not show us the variable attributes of your real dataset(s).

Instead of showing us the transposed dataset (which is much harder to work with), post the original "long" dataset.

Re: How do I output dates that are not in chronological order?

vidya3 — Mon, 03 Apr 2023 07:27:08 GMT

Right. Please find below sas data step.
(Dots indicate missing dates)

Data have;
Input subject day_1 day_2 day_3 day_4 day_5 day_6 day_7 day_8 day_9 day_10;
Cards;
101 10-Jan-22 11-Jan-22 12-Jan-22 13-Jan-22 11-Jan-22 15-Jan-22 16-Jan-22 17-Jan-22 18-Jan-22 19-Jan-22
102 10-Jan-22 11-Jan-22 12-Jan-22 13-Jan-22 14-Jan-22 15-Jan-22 16-Jan-22 17-Jan-22 18-Jan-22 19-Jan-22
103 10-Jan-22 11-Jan-22 . 13-Jan-22 14-Jan-22 15-Jan-22 . 17-Jan-22 18-Jan-22 19-Jan-22
104 26-Jan-22 29-Jan-22 . . . . . 02-Feb-22 03-Feb-22 .
105 26-Jan-22 . . . . . . . . .
106 26-Jan-22 25-Jan-22 . . . . . 02-Feb-22 03-Feb-22 .

Output that I'm expecting:

Subject Day_1 Day_2 Day_3 Day_4 Day_5 Day_6 Day_7 Day_8 Day_9 Day_10 Flag
101 10-Jan-22 11-Jan-22 12-Jan-22 13-Jan-22 11-Jan-22 15-Jan-22 16-Jan-22 17-Jan-22 18-Jan-22 19-Jan-22 Yes
106 26-Jan-22 25-Jan-22 . . . . . 02-Feb-22 03-Feb-22 . Yes

Re: How do I output dates that are not in chronological order?

s_lassen — Mon, 03 Apr 2023 07:33:17 GMT

Something like this, I suppose:

data want;
  set have;
  array dates(*) date_1-date_10;
  do _N_=1 to dim(dates);
    if missing(dates(_N_)) then continue;
    if dates(_N_)<_date then do;
      output;
      leave;
      end;
    _date=dates(_N_);
    end;
  drop _date;
run;

Re: How do I output dates that are not in chronological order?

Kurt_Bremser — Mon, 03 Apr 2023 07:34:31 GMT

Please show the long dataset, and post working code. You need a DATALINES statement and proper informats (use the INFORMAT statement) for at least the dates.

Re: How do I output dates that are not in chronological order?

Kurt_Bremser — Mon, 03 Apr 2023 07:38:59 GMT

I take it that your original dataset contains at least three variables: subject, visit, date.

Sort by subject and visit (if you have visits with missing dates, remove them here with a dataset option); then, compare date with lag(date) to see if there's an incorrect order.

Re: How do I output dates that are not in chronological order?

vidya3 — Mon, 03 Apr 2023 08:29:53 GMT

Thank you for this. This seems to work just fine, but the records with missing dates are getting omitted totally. Any solution for that?