topic Re: Fill in gap years in my dataset in New SAS User

Fill in gap years in my dataset

alsi21ac — Wed, 27 Apr 2022 08:46:27 GMT

Hi all,

I'm after some assistance.

I need to fill in the years in between the observations in the following dataset:

data have; input Country $ Year Disaster $; datalines; Afghanistan 1990 Flood Afghanistan 1993 Epidemic ... Afghanistan 2020 Storm Albania 1992 Landslide Albania 1994 Storm ... Albania 2014 Epidemic ...

I would like to have all the observation for each country ranging from year 1990 to 2020, with a value "None" on the variable disaster for the years in which no disaster was registered. For example:

datalines; Afghanistan 1990 Flood Afghanistan 1991 None Afghanistan 1992 None Afghanistan 1993 Epidemic ... Afghanistan 2020 Storm Albania 1990 None Albania 1991 None Albania 1992 Landslide Albania 1993 None Albania 1994 Storm ... Albania 2014 Epidemic ... Albania 2020 None

Any help would be greatly appreciated .

Re: Fill in gap years in my dataset

PeterClemmensen — Wed, 27 Apr 2022 09:19:38 GMT

Try this

data have;
input Country $1 - 11 Year Disaster :$10.;
datalines;
Afghanistan 1990 Flood     
Afghanistan 1993 Epidemic  
Afghanistan 2020 Storm     
Albania     1992 Landslide 
Albania     1994 Storm     
Albania     2014 Epidemic  
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : 'have');
      h.definekey('Country', 'Year');
      h.definedata('Disaster');
      h.definedone();
   end;

   set have;
   by Country;

   if first.Country then do;
      do Year = 1990 to 2020;
         if h.find() then Disaster = 'None';
         output;
      end;
   end;
   
run;

Re: Fill in gap years in my dataset

Kurt_Bremser — Wed, 27 Apr 2022 09:22:17 GMT

Create a template having all country/year combinations, then update it with have:

data have;
input Country :$20. Year Disaster :$10.;
datalines;
Afghanistan 1990 Flood
Afghanistan 1993 Epidemic
Afghanistan 2020 Storm
Albania 1992 Landslide
Albania 1994 Storm
Albania 2014 Epidemic
;

data years;
do year = 1990 to 2020;
  output;
end;
run;

proc sql;
create table template as
  select distinct
    have.country,
    years.year,
    "None" as disaster length=10
  from have, years
;
quit;

data want;
update template have;
by country year;
run;

Re: Fill in gap years in my dataset

mkeintz — Wed, 27 Apr 2022 20:13:19 GMT

This is a case where use of a temporary array of disasters, indexed by year, and defaulting to 'None" is a nice solution:

data have;
  input Country $ Year Disaster :$9.;
datalines;
Afghanistan 1990 Flood
Afghanistan 1993 Epidemic
Afghanistan 2020 Storm
Albania 1992 Landslide
Albania 1994 Storm
run;

data want;
  set have;
  by country;
  array dhist {1990:2020} $9 _temporary_ (31*'None');
  dhist{year}=disaster;
  if last.country then do year=1990 to 2020;
    disaster=dhist{year};
    output;
    dhist{year}='None';
  end;
run;

The _temporary_ array will have its values (i.e. mostly a series of 'None') retained from one observation to the next.