mergesasdo-loopsdatastep

Create all dates of a month for each id in a SAS dataset


I have a dataset with amounts for May and June dates but some dates are missing. I would like to add the missing dates and later use lag function to add the missing amount

 Have
   ID    DATE         AMT
    1     6/1/2023     $10
    1     6/3/2023     $20
     .
     .
    1     6/12/2023    $15
    1     6/14/2023    $20
    . 
    .
    1     6/30/2023    $20

 Want
   ID    DATE         AMT
    1     6/1/2023     $10
    1     6/2/2023     $10
    1     6/3/2023     $20
     .
     .
    1     6/12/2023    $15
    1     6/13/2023    $15
    1     6/14/2023    $20
    . 
    .
    1     6/30/2023    $20

Can I have some logic to create `Want dataset

this is what I tried

 Proc sql;
  create table uniqueid as select 
  distinct Id from Have;
quit;

data tempdates;
   strdate='01MAY2023'd;
   enddate='30JUN2023'd;
 do date=str to enddate;
   output;
 end;
run;

 data merged;
  merge Have tempdates;
 run;

Solution

  • Use a cross-join...

    proc sql ;
      create table all_id_dates as
      select a.id, b.date, c.amt
      from uniqueid a
           cross join
           tempdates b
           left join
           have c on a.id   = c.id
                 and b.date = c.date
      order by a.id, b.date 
      ;
    quit ;