SAS append in loop

[Souradip]

Thanks for your comments. Here I create a very simple example for the above. Inputs are

Data 1:
Year
2012
2013
2014

Data2:
State year value
A. 2012. 4
A. 2013. 6
B. 2013. 10

Output:
State. Year. Value
A. 2012. 4
A. 2013. 6
A. 2014. 0
B. 2012. 0
B. 2013. 10
B. 2014. 0

We might have a number of states which might not be possible to execute one by one manually.

[Kurt_Bremser · Accepted solution]

Create a lookup table with a cartesian join, then use that in a left join, and do a case when for missing values:

data data1;
input year;
cards;
2012
2013
2014
;
run;

data data2;
input state $ year value;
cards;
A 2012 4
A 2013 6
B 2013 10
;
run;

proc sql;
create table all_states as
select distinct
  a.year,
  b.state
from
  data1 a,
  data2 b
;
create table want as
select
  a.year,
  a.state,
  case
    when b.value is missing then 0
    else b.value
  end as value
from
  all_states a left join data2 b
  on a.year = b.year and a.state = b.state
order by
  a.state,
  a.year
;
quit;

proc print data=want noobs;
run;

Result:

year    state    value

2012      A         4 
2013      A         6 
2014      A         0 
2012      B         0 
2013      B        10 
2014      B         0 

Note how I present example data in data steps with datalines, for easy use by others (just a copy/paste and submit is needed to reliably recreate the dataset). See it as a basic courtesy for potential helpers.

[Souradip]

Thanks a lot for the solution.