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?
proc sort data=transaction; by date; run;
data history;
update history transaction;
by date;
run;
PG
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 .
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
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;
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;
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.