How to Create a Full Daily Transactional Dataset

[c8826024]

I have a limited transaction dataset like this:

Date	Dosage	Status
1/1/2011	20	Live
1/8/2011	10	Live
1/26/2011	15	Dead

I want to create a full daily history transactional dataset like this:

Date	Dosage	Status
1/1/2011	20	Live
1/2/2011	20	Live
1/3/2011	20	Live
1/4/2011	20	Live
1/5/2011	20	Live
1/6/2011	20	Live
1/7/2011	20	Live
1/8/2011	10	Live
1/9/2011	10	Live
1/10/2011	10	Live
1/11/2011	10	Live
1/12/2011	10	Live
1/13/2011	10	Live
1/14/2011	10	Live
1/15/2011	10	Live
1/16/2011	10	Live
1/17/2011	10	Live
1/18/2011	10	Live
1/19/2011	10	Live
1/20/2011	10	Live
1/21/2011	10	Live
1/22/2011	10	Live
1/23/2011	10	Live
1/24/2011	10	Live
1/25/2011	10	Live
1/26/2011	15	Dead
1/27/2011	15	Dead
1/28/2011	15	Dead
1/29/2011	15	Dead
1/30/2011	15	Dead
1/31/2011	15	Dead

Any ideas?

[PGStats]

proc sort data=transaction; by date; run;

data history;

update history transaction;

by date;

run;

PG

[Reeza]

See the many replies to a similar question here:

http://listserv.uga.edu/cgi-bin/wa?A2=ind1203a&L=sas-l&F=&S=&P=6375

Your's is slightly different because you'd like to have the answers flow down.

So I suggest

1. Create a list of dates you'd like using a macro with a start and stop parameters

ie

%let start_date= '1/1/2012'd;

%let end_date='1/31/2012'd;

data list_dates;

     do i=%start_date to %end_date;

          date=i;

     end;

output;

2. Merge this is in with your data set.

proc sql;

create table want as

select ld.date, h.dosage, h.status

from list_dates ld

left join have h

on ld.date=h.date;

3. Use retain and or lag to go through and fill in records when missing.

Lots of examples of those around .

[BobD]

This might get you on the right track:

data have;
   informat date mmddyy10. Dosage 2. Status $4.;
   input Date Dosage Status;
datalines; 
1/1/2011 20 Live
1/8/2011 10 Live
1/26/2011 15 Dead
run;

data calendar;
   do i=1 to 31;
      date = mdy(1,i,2011);
      output;
      end;
   keep date;
run;

data want;
   merge calendar have(in=a rename=(Dosage=tmp_dosage Status=tmp_status));
      by date;
   retain Dosage Status;
   if a then do;
      Dosage = tmp_dosage;
      Status = tmp_status;
      end;
   drop tmp_dosage tmp_status;
run;

I'm sure there are many different ways to do the same thing but this is what comes to my mind first.

Good luck,

Bob

[SASJedi]

This should do it for you:

data have;
   input Date:mmddyy10. Dosage:3. Status:$4.;
   format date mmddyy10.;
datalines;
1/1/2011 20 Live
1/8/2011 10 Live
1/26/2011 15 Dead
;
run;

data want;
   set have end=_last;
   _start=lag1(date);
   _end=date;
   _odose=Dosage;
   _ostat=Status;
   Dosage=lag1(dosage);
   Status=lag1(status);
   if _n_ ne 1 then do;
      do Date=_Start to _end-1;
         output;
      end;
   end;
   if _last then do;
      Dosage=_odose;
      Status=_ostat;
      output;
   end;
   drop _:;
run;

[Patrick]

or this way:

data have;
   informat date mmddyy10. Dosage 2. Status $4.;
   input Date Dosage Status;
datalines; 
1/1/2011 20 Live
1/8/2011 10 Live
1/26/2011 15 Dead
run;

data want(drop=_:);
  merge
    have(rename=(date=_date1))
    have(keep=date rename=(date=_date2) firstobs=2);
  format date date9.;
  do date=_date1 to coalesce(_date2-1,intnx('month',_date1,0,'e'));
    output;
  end;
run;

proc print data=want;
run;

[Haikuo]

Another approach using Hash():

data have;

   informat date mmddyy10. Dosage 2. Status $4.;

   input Date Dosage Status;

datalines;

1/1/2011 20 Live

1/8/2011 10 Live

1/26/2011 15 Dead

run;

data want (drop=_:);

format date mmddyy10.;

     set have (obs=1);

       _start=date;

       _end=intnx('month',date,0,'end');   

     dcl hash hh(dataset:'have');

     hh.definekey('date');

     hh.definedata('dosage','status');

     hh.definedone();

  do date=_start to _end ;

     _rc=hh.find();

      output;

   end;

  run;

proc print;

run;

Haikuo

[MichaelCisse]

This code has been tested and works based on the sample you provided. You can adapt it to a new dataset.

data transdata;
input date mmddyy10. dosage status $;
datalines;
01/1/2011  20 Live
01/8/2011  10 Live
01/26/2011 15 Dead
;

proc print data=transdata;run;

data transdata;
set transdata; duration=intck('day',date,intnx('month',date,0,'e'))+1;
run;

proc print data=transdata; format date date9.;run;

data _null_;
if 0 then set transdata nobs=n;
call symputx('obsn',n);
run;
%put number of observations is &obsn.;

data fulltrans(keep=date dosage status) ;
array durations{&obsn} (7 18 6);
do i=1 to &obsn;
   do j=1 to durations{i};
     set transdata point=i;
     if j>1 then do;
     date=intnx('day',date1,1);
     date1=date;end;
     else date1=date;
     output;
     retain date1;
   end;
end;
stop;
run;

proc print data=fulltrans; format date date9.; run;