Data reformatting question

[hellorc]

closed

[mkeintz]

The approach here is to build a 52-element array of nursing home values for each id, possibly requiring reading in multiple observation per id.  That what the  "do until (last.id)" loop below does, containing a "set have" statement inside it:

data have;
input id age sex $ nursing_home_id covid covid_date :yymmdd10. week1-week52 ;
format covid_date yymmdd10.;
datalines;
1 75 M 0769 1 2021/12/30 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
1 75 M 1650 0 . 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 75 M 3382 0 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0
run;

data want (keep=id age sex nursing_home_id covid week);
  do until (last.id);
    set have (rename=(covid=covid_dummy));
    by id;

    array weeks {52} week1-week52;
    array nh    {52} ;

    do w=1 to 52;
      if weeks{w}=1 then nh{w}=nursing_home_id;
    end;

    if covid_date^=. then do;
      covid_week=ceil((covid_date-'31dec2020'd)/7);
      if covid_week=53 then covid_week=52;
    end;
  end;

  do week=1 to 52;
    if week=covid_week then covid=1;
    else covid=0;
    nursing_home_id=nh{week};
    output;
  end;
run;

And thanks for submitting a "workable" data step (I had to make a couple minor changes to the DATA HAVE step).  So I can say this code has been tested.

[hellorc]

Thank you mkeintz! I will definitely try it out as soon as I am in the office (where SAS is available) Monday! Have a great weekend!

[Ksharp · Accepted solution]

data have;
input id age sex $ nursing_home_id covid covid_date :yymmdd10. week1-week52 ;
format covid_date yymmdd10.;
datalines;
1 75 M 0769 1 2021/12/30 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
1 75 M 1650 0 . 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 75 M 3382 0 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0
;

data temp;
 set have;
 array x{*} week1-week52;
  w=week(covid_date,'w');
  flag=0;
 do week=1 to dim(x);
  v=x{week};
  if w=week then flag=1;
  if v then output;
 end;
keep id age sex nursing_home_id flag week;
run;
proc sort data=temp out=want;
by id age sex week;
run;