Solved
Contributor
Posts: 42

# Data Preparation

Data Have:

 ID DATE TIME Q01 Q02 Q03 100 22-Apr-09 15:00:00 1 0 1 100 22-Apr-09 16:00:00 0 1 1 100 22-Apr-09 17:00:00 1 0 1 100 22-Apr-09 17:00:00 0 1 1 100 22-Apr-09 18:00:00 1 0 1 100 22-Apr-09 18:00:00 0 1 1 100 23-Apr-09 9:00:00 1 0 1 100 23-Apr-09 10:00:00 0 1 1 100 23-Apr-09 11:00:00 1 0 1 100 23-Apr-09 12:00:00 0 1 1 100 23-Apr-09 13:00:00 1 0 1 100 23-Apr-09 14:00:00 0 1 1 100 23-Apr-09 15:00:00 1 0 1 100 23-Apr-09 16:00:00 0 1 1 100 23-Apr-09 17:00:00 1 0 1 100 23-Apr-09 18:00:00 0 1 1 100 26-Apr-09 9:00:00 1 0 1 100 26-Apr-09 10:00:00 0 1 1 100 26-Apr-09 11:00:00 1 0 1 100 26-Apr-09 12:00:00 0 1 1 100 26-Apr-09 13:00:00 1 0 1 100 26-Apr-09 14:00:00 0 1 1 100 26-Apr-09 15:00:00 1 0 1 100 26-Apr-09 16:00:00 0 1 1 100 26-Apr-09 17:00:00 1 0 1 100 26-Apr-09 18:00:00 0 1 1 100 26-Apr-09 19:00:00 1 0 1 100 27-Apr-09 10:00:00 1 1 1

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.

Accepted Solutions
Solution
‎12-16-2013 12:24 PM
Super User
Posts: 8,075

## 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;

All Replies
Solution
‎12-16-2013 12:24 PM
Super User
Posts: 8,075

## 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 and locked.