How to reverse engineer missing dates in SAS dataset-CodePudding

I am trying to reverse engineer some data for a dataset that is only updated weekly, which I want to be daily. What I want is a dataset that is at the fips-day level where the missing days are filled in. For instance we may only have 2022-03-03 and 2022-03-10, but I want a new column (at the fips-day level) where each rate is 1/7th of the rate present in the cell in which we DO have data.

data fips_date;
    input fips $ date :yymmdd10. rate;
    format date ddmmyy10.;
    datalines;
    00065 2022-03-03 30.2
    00066 2022-03-03 23.4
    00067 2022-03-03 14.1
    00065 2022-03-10 28.4
    00066 2022-03-10 21.8
    00067 2022-03-10 15.3
run;

What I want the result to look like is below (original date rows highlighted), however I am a novice SAS user and don't know how to get there. I imagine it would need to be a do-loop or an array of sorts that can be grouped or performed at the fips-date level.

Thank you for any advice or code you may be able to share!

CodePudding user response：

One method but doesn't split equally, uses a spline interpolation:

data fips_date;
input fips $ date :yymmdd10. rate;
format date ddmmyy10.;
datalines;
00065 2022-03-03 30.2
00066 2022-03-03 23.4
00067 2022-03-03 14.1
00065 2022-03-10 28.4
00066 2022-03-10 21.8
00067 2022-03-10 15.3
    ;;;;
run;

proc sort data=fips_date; by fips;
run;


proc expand data=fips_date out=fips_date_daily from=weekly to=daily;
by fips;
id date;
convert rate=daily_rate /observed=total;
run;

And for exactly as posted - but doesn't check for missing weeks, you can do something like this:

proc sort data=fips_date; by fips;
run;

data fips_date_daily;
set fips_date;
by fips;
     do i=0 to 6;
          daily_rate = rate/7;
           date= date i;
           output;
      end;
run;

CodePudding user response：

data want;
   set fips_date;
   do date = date - 6 to date;
      daily_rate = rate / 7;
      output;
   end;
run;