How to merge so that the ID variable begins to repeat

[Mercadja]

Hello all, 

I am at my witts end trying to figure this out. I have a dataset that Has ID Hour Date. If the data I receive does not have each hour (0-23) I need to add it for the ID and Date. If the Date is Missing I also need to add the Date along with the Hour. The Date is on a 7 day block and I run this report once a week.

Here is the code I have to this point:

proc sort data = want;
by ID Hours DATE;
run;

proc means data = want noprint;
by ID Hours DATE;
var Minutes_Since_Previous_Scan;
output out= Look;
run;

proc sort data = Look;
by ID Hours Date;
run;

proc transpose data=Look out=Transpose;
by ID Hours Date;
id _STAT_;
var Minutes_Since_Previous_Scan;
run;

proc sort data = transpose;
by Date Hours;
run;

proc sort data = Insert;
by Date Hours;
run;

data merger;
merge insert transpose ;
by Date Hours;
run;

Problem with this code is it will only insert the hour and date onto the first IDI have to this point. I need it to continue to look at all IDs in the set and continue to add the hour for each date if it is not present. I have made a sheet that would read it but to update the IDs every week only hundreds of IDs is too cumbersome. Any help is appreciated. Thanks, 

[Tom]

To fill in the missing day*hour just take the distinct values of ID and make a skeleton dataset with just those three variables.

Do you know the 7 days?

Do you want 24 hours per day?  From 0 to 23?  

data all;
  set have (keep=id);
  by id;
  if first.id;
  do date=&date1 to &date2 ;
    do hour=0 to 23 ;
      output;
    end;
  end;
run;

data want;
   merge all have ;
   by id date hour ;
run;

What values do you want for the OTHER variables on those new observations?

This example will leave them as MISSING.

[Mercadja]

Thank you for the reply. It is the 7 days of the week based on the report that I am working for. I created an excel file with the dates and the Hours for each date but I do not have the by variable of ID. Is the code you sent still applicable of does it need adjustment with this new information?

[Tom · Accepted solution]

An EXCEL file is not much value, but if you convert it to an actual dataset with DAY and HOUR variables then you can make the skeleton dataset very easily with an SQL cross join query.

So if the set of ID values is in HAVE and the set of DAY*HOUR combinations is in DAYS then the to make the ALL dataset just use:

proc sql;
  create table all as
  select distinct a.id,b.day,b.hour
  from have a 
     , days b
  order by 1,2,3
;
quit;

[Mercadja]

I could kiss you TOM!! Thanks a ton this is exactly what I needed.

[Reeza]

That works but you're still making that Excel file. You really can get around it if you want, up to you though.

[Reeza]

Do you have SAS ETS? For your 7 day period is it relative to the data, when does it start/end?

[Reeza]

I think you may have two problems here, one is merging in summary stats and one is expanding missing information. Can you provide a small sample of your data to illustrate the issue? Fake data is fine. You don't need Excel in the process, SAS can definitely handle this automatically.

I'm not sure how the proc means is incorporated into the problem, which is why the sample data will ensure you get a working solution.