How do I transpose, fill in and maximize

[terjeph]

I have a data file describing users (ID), time periode a service is in use (FromYrMon-ToYrMon) and hours of services (hours). There are two problems. First, the time periods partly overlap. There are also some gaps between the time periods but that is probably correct. Second, the hours of services are not consistently coded in the sense that the same time period (or parts of it) may have differences in hours. The file I want, includes for each ID,  one observation per month, the maximum of hours for that month and 0 for months where there are gaps between time periods. I guess the solution will be something like 1) transposing, 2) fill in gaps and 3) pick the highest number of hours for each month.

data have;
   input ID FromYrMon YYMMN6 ToYrMon YYMMN6. Hours comma4.2;
   datalines;
1 201701 201711 0.75
1 201704 201711 1.20
1 201801 201802 4.00
2 201710 201802 2.00
;

Data want;
ID YYMon Hours
1 201701 0.75
1 201702 0.75
1 201703 0.75
1 201704 1.20
1 201705 1.20
1 201706 1.20
1 201707 1.20
1 201708 1.20
1 201709 1.20
1 201710 1.20
1 201711 1.20
1 201712 0.00 (NB)
1 201801 4.00
1 201802 4.00
2 201701 0.00
2 201702 0.00
2 201703 0.00
2 201704 0.00
2 201705 0.00
2 201706 0.00
2 201707 0.00
2 201708 0.00
2 201709 0.00
2 201710 2.00
2 201711 2.00
2 201712 2.00 
2 201801 2.00
2 201802 2.00

[Ksharp · Accepted solution]

data have;
   input ID FromYrMon :YYMMN6. ToYrMon :YYMMN6. Hours :comma4.2;
format FromYrMon ToYrMon YYMMN6. ;
   datalines;
1 201701 201711 0.75
1 201704 201711 1.20
1 201801 201802 4.00
2 201710 201802 2.00
;
data temp;
 set have;
 do date=FromYrMon to ToYrMon;
   if month(date) ne month(lag_date) then output;
   lag_date=date;
 end;
 keep id date hours;
 format date yymmn6.;
run; 
proc summary data=temp nway;
class id date;
var hours;
output out=temp2(drop=_:) max=;
run;
proc summary data=temp nway;
var date;
output out=min_max(drop=_:) min=min max=max;
run;
data all_month;
 set min_max;
 do date=min to max;
   if month(date) ne month(lag_date) then output;
   lag_date=date;
 end;
 keep date ;
 format date yymmn6.;
run; 
proc sql;
create table want as
select a.*,coalesce(hours,0) as hours
from (select * from (select distinct id from have),all_month) as a 
 natural left join temp2 as b
;
quit;

[terjeph]

Excellent!