SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Expanding to Daily Longitudnal Dataset using two dates

Reply
Occasional Contributor
Posts: 6

Expanding to Daily Longitudnal Dataset using two dates

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. 

Trusted Advisor
Posts: 1,115

Re: Expanding to Daily Longitudnal Dataset using two dates

[ Edited ]

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;
Occasional Contributor
Posts: 6

Re: Expanding to Daily Longitudnal Dataset using two dates

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;

Trusted Advisor
Posts: 1,115

Re: Expanding to Daily Longitudnal Dataset using two dates

[ Edited ]

@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;
Occasional Contributor
Posts: 6

Re: Expanding to Daily Longitudnal Dataset using two dates

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. -##
Super User
Posts: 17,942

Re: Expanding to Daily Longitudnal Dataset using two dates

I don't get a double copy.

 

Post your actual code.

Trusted Advisor
Posts: 1,115

Re: Expanding to Daily Longitudnal Dataset using two dates

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."

Super User
Posts: 10,548

Re: Expanding to Daily Longitudnal Dataset using two dates


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;

Ask a Question
Discussion stats
  • 7 replies
  • 372 views
  • 2 likes
  • 4 in conversation