Probably not the most efficient but I think it will work for you.
data work.have;
infile datalines;
input Action $ State $ Date1 :mmddyy10. Date2 :mmddyy10.;
format Date1 mmddyy10. Date2 mmddyy10.;
datalines;
Walk AK 04/01/2020 04/09/2020
Run AL 03/30/2020 04/03/2020
Walk AR 03/26/2020 04/02/2020
Walk AZ 04/03/2020 04/08/2020
Sit CA 03/28/2020 04/01/2020
Run CO 04/01/2020 04/09/2020
;
run;
data expand_have;
set have;
format date mmddyy10.;
do date = date1 to date2;
action_value=0;
if action="Run" then action_value=2;
else if action="Walk" then action_value=1;
else if action="Sit" then action_value=3;
output;
end;
drop action date1 date2;
run;
proc sql noprint;
create table min_max as
select min(date1) as min_date1 format=mmddyy10.,
max(date1) as max_date1 format=mmddyy10.,
min(date2) as min_date2 format=mmddyy10.,
max(date2) as max_date2 format=mmddyy10.
from have;
select min(min_date1,min_date2) into :min_date from min_max;
select max(max_date1,max_date2) into :max_date from min_max;
select distinct(state) into :states separated by ',' from have;
select count(distinct(state)) into :statecount from have;
quit;
run;
data _null_;
put "States is &states.";
put "Count is &statecount.";
put "Min date is &min_date.";
put "Max date is &max_date.";
stop;
run;
data stage;
format date mmddyy10.;
format state $2.;
states = "&states.";
do date = &min_date to &max_date;
do i = 1 to &statecount;
state = trim(scan(states,i));
output;
end;
end;
stop;
drop states i;
run;
proc sql;
create table need as
select a.*,coalesce(b.action_value,0) as action from stage a left join expand_have b
on a.state = b.state and a.date=b.date
order by date, state;
quit;
run;
... View more