topic Create a Row for Every Date within a Date Range in SAS Programming

Create a Row for Every Date within a Date Range

djbateman — Wed, 08 Jun 2022 14:45:34 GMT

I have created a process that makes a row for every date in a date range. It is very tedious and slow when working with large data. Can someone help me simply and speed up this process?

As a very small scale example, suppose I have this dataset:

proc sql;
	create table have (SUBJID char(7), STDT num format=date9., ENDT num format=date9.);
	insert into have (subjid, stdt, endt)
		values ('101-001','01JAN2022'd,'07JAN2022'd)
		values ('101-002','01FEB2022'd,'28FEB2022'd)
		values ('101-003','01JAN2022'd,'14JAN2022'd)
		values ('101-004','01JAN2022'd,'03JAN2022'd);
quit;

I want to get a record for every date within the start and end dates. So, if the subject spans 3 days, then I want to turn it into 3 records where each record contains a date within that range. This is what I am doing to get what I want:

data have1;
	set have;
	REPS=endt-stdt+1;
run;



%macro replicate;
	proc sql noprint;
        select count(*) into :nrec trimmed from have1;
		select subjid, reps into :subj1-:subj&nrec., :rep1-:rep&nrec. from have1;
	quit;

	data want0;
		set %do i=1 %to &nrec.;
				%do j=1 %to &&rep&i.;
					have1 (where=(subjid="&&subj&i."))
				%end;
			%end;;
	run;
%mend replicate;

%replicate;



data want;
	set want0;
	by subjid;
	retain DATE;
	if first.subjid then date=stdt;
		else date=date+1;
	format date date9.;
run;
proc sort data=want;
	by subjid date;
run;

This process works, but it isn't very effieicent for large date ranges. I have some ranges that are up to 7000 days, so I am looping A LOT. Is there a quicker/cleaner way to do this same method that won't take so much time?

Re: Create a Row for Every Date within a Date Range

Astounding — Wed, 08 Jun 2022 14:54:12 GMT

If you have one observation per SUBJID (not clear from your post), why add all the complications? Simply:

data want;
   set have;
   do date = stdt to endt;
      output;
   end;
   drop stdt enddt;
run;

If this doesn't do the trick exactly, it should at least give you a starting point to consider.

Re: Create a Row for Every Date within a Date Range

djbateman — Wed, 08 Jun 2022 14:58:28 GMT

I actually have more than just SUBJID as BY variables in my real process because I'm working with ConMed data. There is also a sequence ID, so a subject can have more than one record (and the date ranges could overlap). I just figured I could take whatever solution someone else has and add in the sequence variable as well. I'll play around with your solution (much simpler than what I have) and see if I can use it with my sequence variable added.

Re: Create a Row for Every Date within a Date Range

Tom — Wed, 08 Jun 2022 15:12:56 GMT

It is trivial given your example data.

data have;
  input subjid :$7. stdt :date. endt :date.;
  format stdt endt date9.;
cards;
101-001 01JAN2022 07JAN2022
101-002 01FEB2022 28FEB2022
101-003 01JAN2022 14JAN2022
101-004 01JAN2022 03JAN2022
;

Just a simple DO loop.

data want;
  set have;
  do date=stdt to endt ;
    output;
  end;
  format date date9.;
run;

If your real problem is more complex (multiple observations per ID, overlapping date ranges, etc) then perhaps you just want to use an array to mark which dates are covered.

%let mindt=%sysevalf('01JAN2000'd);
%let maxdt=%sysevalf('31DEC2025'd);

data want ;
   set have;
   by subjid ;
   array _d [&mindt:&maxdt] _temporary_ ;
   if first.subjid then call missing(of _d(*));
   do date=max(stdt,&mindt) to min(endt,&maxdt);
      _d[date]=1;
   end;
   if last.subjid then do date=&mindt to &maxdt ;
      if _d[date] then output;
   end;
   keep subjid date ;
   format date date9.;
run;

Re: Create a Row for Every Date within a Date Range

Tom — Wed, 08 Jun 2022 15:11:41 GMT

@djbateman wrote:

I actually have more than just SUBJID as BY variables in my real process because I'm working with ConMed data. There is also a sequence ID, so a subject can have more than one record (and the date ranges could overlap). I just figured I could take whatever solution someone else has and add in the sequence variable as well. I'll play around with your solution (much simpler than what I have) and see if I can use it with my sequence variable added.

Sounds like the goal is NOT to generate one observation per date.

Instead it looks like you want to collapse potentially overlapping periods into a new set of non-overlapping periods.

That is a question that has been asked and answered before. Search around.