topic Re: Expanding to Daily Longitudnal Dataset using two dates in SAS Data Management

Expanding to Daily Longitudnal Dataset using two dates

vldicker — Wed, 06 Apr 2016 18:20:03 GMT

I need to take my dataset which provides two days and create a line for every date between the two dates for each person.

Example Starting Data

ID Date1 Date2

1 4/4/16 4/6/16

2 7/8/16 7/9/16

Example Finished Dataset

ID Date Date1 Date2

1 4/4/16 4/4/16 4/6/16

1 4/5/16 4/4/16 4/6/16

1 4/6/16 4/4/16 4/6/16

2 7/8/16 7/8/16 7/9/16

2 7/9/16 7/8/16 7/9/16

Date being the new variable in the dataset.

Re: Expanding to Daily Longitudnal Dataset using two dates

FreelanceReinh — Wed, 06 Apr 2016 18:38:54 GMT

Try this:

data have;
input ID (Date1 Date2) (:mmddyy.);
cards;
1 4/4/16 4/6/16
2 7/8/16 7/9/16
;

data want;
set have;
do Date=date1 to date2;
  output;
end;
format date: mmddyy8.;
run;

Edit: If you don't like the column order in dataset WANT, you can insert the following line after the data want; statement:

retain ID Date Date1 Date2;

Re: Expanding to Daily Longitudnal Dataset using two dates

vldicker — Wed, 06 Apr 2016 18:42:29 GMT

I actually figured it out thanks to Freelance Reinhard

Date Want;

set have;

by id;

if first.id then do;

date=date1;

output;

end;

else;

date=date1;

do unitl (date2);

date+1;

output;

end;

format date mmddyy10.;

run;

Re: Expanding to Daily Longitudnal Dataset using two dates

FreelanceReinh — Wed, 06 Apr 2016 19:10:33 GMT

@vldicker: I'm afraid that your code doesn't work. Typos ("Date" in line 1, "unitl") apart, the UNTIL condition is incorrect: It is equivalent to "date2 is non-missing and not zero." In most cases (incl. your sample data!) this will be satisfied immediately, so that the body of the DO UNTIL loop is executed only once -- irrespective of the difference between date1 and date2.

Edit: Also, the empty ELSE statement does probably not do what you intended (but simply nothing).

Edit 2: Here's a corrected version of your approach:

data want;
set have;
date=date1;
do until(date>date2);
  output;
  date+1;
end;
format date mmddyy10.;
run;

Re: Expanding to Daily Longitudnal Dataset using two dates

vldicker — Wed, 06 Apr 2016 19:32:40 GMT

So your code worked the fastest when I tried what you replied with. But it
makes a double copy of everything (which can be deleted with a proc sort)
but is there a reason it does that?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Expanding to Daily Longitudnal Dataset using two dates

Reeza — Wed, 06 Apr 2016 20:19:48 GMT

I don't get a double copy.

Post your actual code.

Re: Expanding to Daily Longitudnal Dataset using two dates

FreelanceReinh — Wed, 06 Apr 2016 20:30:08 GMT

Thanks, @Reeza, for stepping in. I must have overlooked the notification that vldicker replied again. There's so much going on in the forum currently.

@vldicker: Sorry for the delayed reply. Are you using two OUTPUT statements (as in your suggested code)? This might explain the "double copies."

Re: Expanding to Daily Longitudnal Dataset using two dates

ballardw — Wed, 06 Apr 2016 20:30:21 GMT

@vldicker wrote:
So your code worked the fastest when I tried what you replied with. But it
makes a double copy of everything (which can be deleted with a proc sort)
but is there a reason it does that?

Your code had two output statements. So you get one with the first value of id and then because your ELSE doesn't include any statements you get a second for the first value of id.

Date Want;

set have;

by id;

if first.id then do;

date=date1;

output;

end;

else;

date=date1;

do unitl (date2);

date+1;

output;

end;

format date mmddyy10.;

run;