BookmarkSubscribeRSS Feed
vidya3
Calcite | Level 5
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
6 REPLIES 6
Kurt_Bremser
Super User

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.

vidya3
Calcite | Level 5
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
s_lassen
Meteorite | Level 14

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;          
vidya3
Calcite | Level 5
Thank you for this. This seems to work just fine, but the records with missing dates are getting omitted totally. Any solution for that?
Kurt_Bremser
Super User

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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 432 views
  • 0 likes
  • 3 in conversation