Subset a data based on values of variables

[sarahsasuser]

Dear All,

I have a dataset with hundreds of dates  and doses of medications. DATE1 has a corresponding DOSE1 and so on. I have passed the values of the dates into new variables based on each person's start date. I would also like the dose values that correspond to the dates to get passed to new variables. For each person, I want the dates that occur on or after the STARTDATE to be passed into new variables called NEW_DATES1, NEW_DATES2, etc. 

 HAVE

ID	STARTDATE	DATE1	DOSE1	DATE2	DOSE2	DATE3	DOSE3
1	10/25/2000	1/2/1999	20	11/2/2000	20	3/5/2001	50
2	5/16/2005	6/14/2001	15	7/5/2002	100	5/25/2006	10
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25

Want

ID	STARTDATE	NEW_DATE1	NEW_DOSE1	NEW_DATE2	NEW_DOSE2	NEW_DATE3	NEW_DOSE3
1	10/25/2000	11/2/2000	20	3/5/2001	50	.	.
2	5/16/2005	5/25/2006	10	.	.	.	.
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25

I was able to pass the old dates to the new dates, but need help with the doses:

array dx (*) date1 - date800;

array dt (*) new_date1 - new_date800
do j = 1 to dim(dx) UNTIL (dx(j)=. );
       if dx(j) >= startdate then do;
          do k=1 to 614-j;
             dt(k) = dx(j);
             j= j+1;
          end;
       end;
end;

Thanks!

Sarah

[Reeza · Accepted solution]

Transpose to a long data set, having a single variable for date and dose and a Event record.

ID StartDate Dose EventDate Event

1 10/25/2000 20 1/2/1999 1

1 10/25/2000 20 11/2/2000 2

1 10/25/2000 50 5/25/2006 3

2 5/16/2005 15 6/14/2001 1

...

....

Now, delete records where the event date is greater than the start date. 

Then you can recreate the EVENT variable based on your new observations

If you really want a wide format, then you would re-transpose it back to wide, though in general, a long format is easier to work with.

---

@sarahsasuser wrote:

Dear All,

I have a dataset with hundreds of dates  and doses of medications. DATE1 has a corresponding DOSE1 and so on. I have passed the values of the dates into new variables based on each person's start date. I would also like the dose values that correspond to the dates to get passed to new variables. For each person, I want the dates that occur on or after the STARTDATE to be passed into new variables called NEW_DATES1, NEW_DATES2, etc. 

 HAVE

ID	STARTDATE	DATE1	DOSE1	DATE2	DOSE2	DATE3	DOSE3
1	10/25/2000	1/2/1999	20	11/2/2000	20	3/5/2001	50
2	5/16/2005	6/14/2001	15	7/5/2002	100	5/25/2006	10
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25

Want

ID	STARTDATE	NEW_DATE1	NEW_DOSE1	NEW_DATE2	NEW_DOSE2	NEW_DATE3	NEW_DOSE3
1	10/25/2000	11/2/2000	20	3/5/2001	50	.	.
2	5/16/2005	5/25/2006	10	.	.	.	.
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25

I was able to pass the old dates to the new dates, but need help with the doses:

array dx (*) date1 - date800;

array dt (*) new_date1 - new_date800
do j = 1 to dim(dx) UNTIL (dx(j)=. );
       if dx(j) >= startdate then do;
          do k=1 to 614-j;
             dt(k) = dx(j);
             j= j+1;
          end;
       end;
end;

Thanks!

Sarah

---

You can do it in a single data step it's just a bit tedious and not dynamic. If your dates only go to THREE that's not an issue. Is it possible to have ties with your dates?

[novinosrin]

data HAVE;
input ID	STARTDATE :mmddyy10.	DATE1 :mmddyy10.	DOSE1	DATE2 :mmddyy10.	DOSE2	DATE3 :mmddyy10. DOSE3;
format date: mmddyy10.;
cards;
1	10/25/2000	1/2/1999	20	11/2/2000	20	3/5/2001	50
2	5/16/2005	6/14/2001	15	7/5/2002	100	5/25/2006	10
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25
;

data want;
set have;
n=1;
array dt(*) date1-date3;
array new_dt (*) new_date1 - new_date3;
array dx(*) dose1-dose3;
array new_dx(*) newdose1-newdose3;

do j=1 to dim(dx);
if dt(j) >= startdate then 
do;
	new_dt(n)=dt(j);
	new_dx(n)=dx(j);
	n+1;
end;
end;
format new_date: mmddyy10.;
drop n j;
run;

[novinosrin]

data HAVE;
input ID	STARTDATE :mmddyy10.	DATE1 :mmddyy10.	DOSE1	DATE2 :mmddyy10.	DOSE2	DATE3 :mmddyy10. DOSE3;
format STARTDATE date: mmddyy10.;
cards;
1	10/25/2000	1/2/1999	20	11/2/2000	20	3/5/2001	50
2	5/16/2005	6/14/2001	15	7/5/2002	100	5/25/2006	10
3	1/18/2004	1/19/2004	25	2/24/2005	20	8/16/2005	25
;

data temp;
set have;
array dt(*) date1--dose3;
do n=1 by 2 to dim(dt);
if dt(n) >= startdate then 
do;
_n+1;
d=dt(n);
grp=cats('new_date',_n);
output;
d=dt(n+1);
grp=cats('new_dose',_n);
output;
end;
end;
_n=0;
keep id startdate d grp;
run;

proc transpose data=temp out=want(drop=_name_);
by id STARTDATE;
var d ;
id grp;
format Date: mmddyy10.;
run;