use the observation of the existence year for four adjacent years (empty)

[Alexxxxxxx]

Hi,

For table1, I only have the observation for the year 1990, 1995, 2000, 2005 for each country. I am trying to add the values for all years (1990-2005).

Patent_rights_index	Countries	year
2.45	ALGERIA	1990
2.35	ALGERIA	1995
2.65	ALGERIA	2000
2.775	ALGERIA	2005
0	ANGOLA	1990
0.875	ANGOLA	1995
1.075	ANGOLA	2000
1.2	ANGOLA	2005

By using the first country Algeria as an example, I expect to use the observation of the existence year (i.e., 1990,1995, 2000, 2005) for four adjacent years. For example, the observation for the year 1990 is used for the year 1991, 1992; the year 1993, 1994, 1996 and 1997 are set equal to the observation for the year 1995. they are expected to look like below,

Patent_rights_index	Countries	year
2.45	ALGERIA	1990
2.45	ALGERIA	1991
2.45	ALGERIA	1992
2.35	ALGERIA	1993
2.35	ALGERIA	1994
2.35	ALGERIA	1995
2.35	ALGERIA	1996
2.35	ALGERIA	1997
2.65	ALGERIA	1998
2.65	ALGERIA	1999
2.65	ALGERIA	2000
2.65	ALGERIA	2001
2.65	ALGERIA	2002
2.775	ALGERIA	2003
2.775	ALGERIA	2004
2.775	ALGERIA	2005

Could you please give me some suggestion about this?

data table1;
	infile cards dsd  dlm=",";
	input
	Patent_rights_index	：8.
	Countries :$50.
	year ：8.
   ;
	cards;
2.45，ALGERIA，1990
2.35，ALGERIA，1995
2.65，ALGERIA，2000
2.775，ALGERIA，2005
0，ANGOLA，1990
0.875，ANGOLA，1995
1.075，ANGOLA，2000
1.2，ANGOLA，2005
;;;;
run;

[andreas_lds · Accepted solution]

The following code assumes that the data is at least grouped by "Countries", i have removed fancy unicode-chars from the datastep you have posted.

data table1;
   infile cards dsd dlm=",";
   input
   Patent_rights_index :8.
   Countries :$50.
   year :8.
   ;
   cards;
2.45,ALGERIA,1990
2.35,ALGERIA,1995
2.65,ALGERIA,2000
2.775,ALGERIA,2005
2.45,BELGIUM,1990
2.05,BELGIUM,1995
2.95,BELGIUM,2000
1.44,BELGIUM,2005
0,ANGOLA,1990
0.875,ANGOLA,1995
1.075,ANGOLA,2000
1.2,ANGOLA,2005
;;;;
run;

data want;
   set table1;
   by Countries notsorted;
      
   if first.Countries then do;
      output;
      do year = year + 1 to year + 2;
         output;
      end;
   end;
   else do;
      do year = year - 2 to year + ifn(last.Countries, 0, 2);
         output;
      end;
   end;
run;