BookmarkSubscribeRSS Feed
c8826024
Calcite | Level 5

I have a limited transaction dataset like this:

DateDosageStatus
1/1/201120Live
1/8/201110Live
1/26/201115Dead

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

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

Any ideas?

7 REPLIES 7
PGStats
Opal | Level 21

proc sort data=transaction; by date; run;

data history;

update history transaction;

by date;

run;

PG

PG
Reeza
Super User

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 Smiley Happy.

BobD
Fluorite | Level 6

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
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users
Patrick
Opal | Level 21

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
Onyx | Level 15

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1240 views
  • 2 likes
  • 8 in conversation