topic Re: How to Create a Full Daily Transactional Dataset in SAS Programming

How to Create a Full Daily Transactional Dataset

c8826024 — Fri, 02 Mar 2012 21:56:48 GMT

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?

How to Create a Full Daily Transactional Dataset

PGStats — Fri, 02 Mar 2012 22:26:00 GMT

proc sort data=transaction; by date; run;

data history;

update history transaction;

by date;

run;

How to Create a Full Daily Transactional Dataset

Reeza — Fri, 02 Mar 2012 22:27:20 GMT

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

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

Re: How to Create a Full Daily Transactional Dataset

BobD — Sat, 03 Mar 2012 00:29:15 GMT

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

How to Create a Full Daily Transactional Dataset

SASJedi — Sat, 03 Mar 2012 01:43:20 GMT

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;

How to Create a Full Daily Transactional Dataset

Patrick — Sat, 03 Mar 2012 02:36:12 GMT

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;

Re: How to Create a Full Daily Transactional Dataset

Haikuo — Sat, 03 Mar 2012 07:28:32 GMT

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

Re: How to Create a Full Daily Transactional Dataset

MichaelCisse — Tue, 06 Mar 2012 00:10:16 GMT

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;