Transforming a dataset and calculating variables with dates

[wernie]

Hi all,

I attached an example dataset similar to what I have and what I want. Someone will be sending me data, so I want to make it as easy as possible to process without having to manually go in and change dates, etc.

I'll have a written out 'action' and then each state (the example dataset just has a few states). Then there will be two sets of dates.

What I want to do is create a daily dataset starting at some date that I specify and have each state listed for each date (I learned how to do just this part yesterday) to a specified end date. What I'm not sure how to do is then go through each state and say if it's between date1 and date2 then list out 1 (or whatever the action value should be) and if it's not between those dates, then make it a 0. I have this shown in my example 'want' dataset in the spreadsheet (where 0=no action, 1=walk, 2=run, 3=sit).

I can't think of how to do this with macros to output everything and not overwrite each state as I want a complete dataset. I'm not sure how often I'll be getting an updated spreadsheet to run through, so anything to make updating it easiest is best.

Thanks!

[PaigeMiller]

Many of us will not (or cannot) download Excel or other Microsoft Office documents. Better you should provide your data as SAS data step code. Here are instructions to provide your data as SAS data step code: How to convert datasets to data steps

[wernie]

Oops, here is the data as data step code!

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;

I don't have the code for the data that I want (obviously), but I put a screenshot of what that looks like so it's PDF instead of Excel to give an idea of what I'm trying to get. Thanks!

[Daryl]

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;