Data Preparation

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Data Preparation

Data Have:

ID DATE TIMEQ01Q02Q03
10022-Apr-0915:00:00101
10022-Apr-0916:00:00011
10022-Apr-0917:00:00101
10022-Apr-0917:00:00011
10022-Apr-0918:00:00101
10022-Apr-0918:00:00011
10023-Apr-099:00:00101
10023-Apr-0910:00:00011
10023-Apr-0911:00:00101
10023-Apr-0912:00:00011
10023-Apr-0913:00:00101
10023-Apr-0914:00:00011
10023-Apr-0915:00:00101
10023-Apr-0916:00:00011
10023-Apr-0917:00:00101
10023-Apr-0918:00:00011
10026-Apr-099:00:00101
10026-Apr-0910:00:00011
10026-Apr-0911:00:00101
10026-Apr-0912:00:00011
10026-Apr-0913:00:00101
10026-Apr-0914:00:00011
10026-Apr-0915:00:00101
10026-Apr-0916:00:00011
10026-Apr-0917:00:00101
10026-Apr-0918:00:00011
10026-Apr-0919:00:00101
10027-Apr-0910:00:00111

Data Want:

Based on the Date, first date here is 22-Apr-2009, last date is 27-Apr-2009, for each day between April 22-27, has time from 9 am-6 pm, 10 time points. If any day between 22-27 is not there, need to add,  if any time point for a day is not there, need to add.

This is only for ID = 100, there will be hundreds of IDs like this, each ID has different start date and end date, but each day all have fixed time points 9 am - 6 pm. And can be repeated for any time points for the same day. 

Hope I explained clearly.

Thank you for your help.


Accepted Solutions
Solution
‎12-16-2013 12:24 PM
Super User
Super User
Posts: 6,502

Re: Data Preparation

Make a dummy table and merge in your existing data.

Since the time points are fixed use a simple do loop to create them.

do time='09:00't to '18:00't by '01:00't ;

But for the days you need to find the start and end dates for each subject.

data frame ;

   set have (keep =id  date) ;

   by id date;

    retain start;

   if first.id then start=date;

   if last.id then do date=start to date;

     do time='09:00't to '18:00't by '01:00't ;

       output;

     end;

    end;

    keep id date time;

run;

data want ;

   merge frame have ;

   by id date time;

run;

View solution in original post


All Replies
Solution
‎12-16-2013 12:24 PM
Super User
Super User
Posts: 6,502

Re: Data Preparation

Make a dummy table and merge in your existing data.

Since the time points are fixed use a simple do loop to create them.

do time='09:00't to '18:00't by '01:00't ;

But for the days you need to find the start and end dates for each subject.

data frame ;

   set have (keep =id  date) ;

   by id date;

    retain start;

   if first.id then start=date;

   if last.id then do date=start to date;

     do time='09:00't to '18:00't by '01:00't ;

       output;

     end;

    end;

    keep id date time;

run;

data want ;

   merge frame have ;

   by id date time;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 232 views
  • 0 likes
  • 2 in conversation